Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Referencing data on one spreadsheet from a second spreadsheet

Posted on 2014-03-13
6
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 33

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 33

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

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 33

Accepted Solution

by:
Rob Henson earned 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

715 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