Links to outside spreadhseets (works with one, not the other)

[mhays]

I am in Excel 2010.

I have a template that pulls information from two separate spreadsheets, based on input parameters. One works just fine and will update when teh parameters are changed.

One, however, requires the spreadhseet be open to show correct data. For example, I open the source doc, and get #VALUE errors until i double click on a cell to open the referenced spreadsheet. At that point the data fills.



Any ideas how to get this to work without needing to open the spreadsheet?

Thanks!

[Catalin.B]

There are some functions that will not work on closed workbooks.
A simple workaround is to copy the source sheet in the destination workbook, and make simple links between source and destination cells. ( in case you don't know this method: To copy sheets, with both workbooks open, right click the source sheet tab, select the destination workbook, check the make a copy option, enter.) Then make links from this copy to source, to get all the needed data. Refer your formulas to this copy of your source sheet, you will never get that type of errors... Whenever you open this destination workbook, the links will automatically be updated and you will get the latest data without opening the source. (if the settings are set to update automatically at open, in options excel, and in edit--> links--> startup prompt--excel 2003, or from data-->edit links--> startup prompt)

[mhays]

OK, ill have to give that workaround a try. I just dont get why it works with a different source workbook within the same destination workbook...