Using the INDIRECT function I'm trying to reference data in another Workbook. However, if that workbook is not open the function returns a #REF! value. Open the Workbook it's referencing and it happily returns the vlaue I need. I understand it's one of the volatile functions in Excel, but I don't know why it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Thats in the specification for this funtion in the online help: If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

You could put the reference data in the same workbook on another worksheet. Or create another workbook with VB script, use this as a controlling workbook to open both workbooks.

Another low tech idea, place a big textbox in the workbook with a note to remind you to open the other workbook.

For data from a closed file, John Walkenbach offers a tip via VBA using a function from an old version of Excel. http://www.j-walk.com/ss/excel/tips/tip82.htm HTH