Solved

Referencing data on one spreadsheet from a second spreadsheet

Posted on 2014-03-13
6
225 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 32

Expert Comment

by:Rob Henson
ID: 39926506
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
 
LVL 1

Author Comment

by:dma70
ID: 39926609
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 32

Expert Comment

by:Rob Henson
ID: 39926629
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 39926639
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
 
LVL 1

Author Comment

by:dma70
ID: 39926651
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 32

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 39926663
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Graph 3 45
Excel VBA - Returning results from batch file 9 65
Help with excell ... 6 58
splitting text of cell to columns 14 24
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

932 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