Use INDIRECT function with network path

[waynerenaud]

I want to use the indirect function to obtain data from another workbook on the network.
workbook on my pc = local.xls
workbook on server = P:\test\server.xls (P: is a network share with all necessary permissions)
server.xls has 3 worksheets named QLD NSW VIC
cell A1 in QLD = 5
cell A1 in NSW = 10
cell A1 in VIC = 15

in my local.xls
cell A1 = QLD
cell A2 = NSW
cell A3 = VIC

in column B I want a formula to pull the data from the correct worksheet in server.xls
I tried this in cell B1
=INDIRECT("'P:\test\[server.xls]"&A1&"'!$A$1")
but I get a #REF!.
If I evaluate the formula it steps through creating the full path "'P:\test\[server.xls]QLD'!$A$1" (it does show the speech marks in the evaluation??) then gives #REF
Am I doing something wrong or is it impossible to use this across workbooks or network paths.
(If I copy the worksheets to the local.xls and adjust the formula, it does work correctly).
Is there another way to do this as the server.xls runs various macros pulling data from html files, some of which is needed in other workbooks. Thanks

 

[xlbo]

INDIRECT only works on OPEN workbooks

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[waynerenaud]

Thanks for the lightning quick response xlbo, works fine with the other workbook open