• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating links

L

LadyBlack

New Member
Hello all


I am doing another chart! I've just discovered that what I'm actually doing is an Excel dashboard, which I will be taking a course for next month. However, I'm trying to do some for my boss's next meeting this month, and am stuck.


I am trying to link one worksheet to another. I have a chart to show the various bits of work being done over the month, and this is linked to the forecast chart (obviously, work done = income which then goes on the forecast). I've managed to get the Forecast sheet to show how much of a certain type of work is done every month, and I want to put the same information on the new Monthly chart. The path from the Monthly Report to the Forecast is fine, however, anytime the Forecast sheet is closed, and I do anything at all to the Monthly report, all my figures vanish and I get #VALUE! in my cells. Of course, my graph then disappears too.


The links are set to update automatically, 'update remote references' is ticked and I get asked whether I want to update or not anyway. Why does it therefore NOT update, and will only do so when Forecast is open? Or am I trying to get Excel to do too much, and it WILL only update when Forecast is open at the same time?


Many thanks in advance
 
Hello LabyBlack,


Unfortunately, the external sheet must be opened for you to see the value in the Monthly Report.


Another way to do so, is to "import" from an external Data Source.

This way, use the Microsoft SQL Query with an XLS File and import the Forecast sheet in a new sheet of your monthly report.


This sheet can then be hidden (right click on the sheet tab / Hide). And you will be able to use the data the same way internal cells would have let you do so.


Cyril.
 
This is probably the best summation of accessing data from closed workbooks I've seen


http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/


The comments have lots of extra help
 
Hi Hui,


I'm particularly found of "Open the second file hidden" comment from Jon Peltier.


Put this in the Workbook_Open function.
 
Hello all


Very quick post to say thank you, I was snowed under for the last few days! I am off to read the information, looks like I have some work ahead! It might take some time, so I thought I'd say thank you now, and try and sort everything out later.
 
You must log in or register to reply here.
Back
Top