Solved

Referencing data on one spreadsheet from a second spreadsheet

Posted on 2014-03-13
6
224 Views
Last Modified: 2014-03-13
I keep data I use in various spreadsheets in one spreadsheet called "dataheader_ust".   The other spreadsheets  (example: wtc.xlsm) refer to information on "dataheader_ust" using references like:  
='P:\weekly qs\[dateheader_ust.xlsm]Sheet1'!A1

I use manual calc.  I find when I update the other spreadsheet (e.g. wtc.xlsm) the data from dataheader_ust is not updated.    I can usually get it to update by recopying and pasting the formula, or filling from another nearby cell, but not buy using CTRL-f9.

Is there a reliable way to ensure that all references to outside spreadsheets update?

Also - I always open the dataheader_ust spreadsheet in the same session as the spreadsheet that uses the data.    Is that necesssary?  Or, can wtc.xlsm get data from dataheader_ust when dataheader_ust is not open?
0
Comment
Question by:dma70
  • 4
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
If source and destination files are open at the same time, the recalculation event should update the files. Have you tried using just F9 to force a full Calculation? Not sure what Ctrl + F9 does; I have seen Shift + F9 for current sheet calculation.

If the source document is closed, then use the Edit Links window to update values for the workbook if the links were not updated when destination file was opened. Once it has updated once if the source file has bot been updated then it won't need another refresh; the data in the source file will not have changed.

Thanks
Rob H
0
 

Author Comment

by:dma70
Comment Utility
Sorry I meant shift-F9.    Cant use F9 because I don't want to calc the entire worksheet, only want to calc one sheet at a time.  

For whatever reason, shift-f9 does not update the data.  Perhaps spreadsheet thinks it was already updated.  It only updates when I repaste the formula in the cells. Also, when the update occurs,  does it take data from the stored file of from the open spreadsheet?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
If you have the source file open it will update from the open copy. To check you have the correct version open, the formula linking to the source file will only show the filename, sheetname and cell reference:

=[dateheader_ust.xlsm]Sheet1'!A1

If the source file is not open it will show with its full path as well as filename:

='P:\weekly qs\[dateheader_ust.xlsm]Sheet1'!A1

Thanks
Rob H
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Does the formula that you are wanting to be updated link directly to the source file or is it via another formula on another sheet within the workbook? If so, you will have to recalculate that sheet as well.

Thanks
Rob H
0
 

Author Comment

by:dma70
Comment Utility
The comment on how the link displays was helpful.  The link is direct and does not go thru another sheet in the workbook.  However other sheets in the workbook look to the date updated on the first sheet.

How does the spreadsheet know it has recently updated a cell.  Perhaps when I open the file, it still thinks it is up-to-date, and somehow shift-F9 doesn't ensure the cells are recalculated.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
Comment Utility
As I said before, if the source file is open the link will be to the open file but will only update when a recalculation or update through Edit Links window is done.

Shift F9 will only recalculate the current sheet, maybe you need to recalculate the source file as well if that has changed.

Thanks
Rob H
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now