Solved

Referencing data on one spreadsheet from a second spreadsheet

Posted on 2014-03-13
6
231 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 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

837 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