• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Referencing data on one spreadsheet from a second spreadsheet

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
dma70
Asked:
dma70
  • 4
  • 2
1 Solution
 
Rob HensonFinance AnalystCommented:
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
 
dma70Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rob HensonFinance AnalystCommented:
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
 
dma70Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now