reference to a particular cell in a closed workbook (indirect)

[sabi.kiss]

Hi All,

I am trying to reference to a particular cell in a close workbook, using the indirect fuction, however I only get a #REF! result. Could you pelase assist on where I'm going wrong?

INDIRECT("'C:\Users\USER\Desktop\FOLDER1\["&$B3&"].xlsm ["&$D3&"]'!", B$69)

The value of B3 will decided the file name I would like to get the reference from.
The value of D3 will have the name of the tab and B69 will be the cell I'm referencing to.

Hope it make sense.

Thanks,
Sabi

 

[sijpie]

='C:\Users\xyz\Documents\Performance\[KCAD Well Depths (2011).xlsx]Summary'!$E$10

you have got your brackets wrong

 

[VoG]

INDIRECT does not work with closed workbooks. Try Harlan Groves' PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

 

[sabi.kiss]

Thanks for the reference VoG, much appreciated.

 

[sabi.kiss]

One more question please.
The Pull Function workes perfectly, however every single time I do an amendment on one of the worksheets, the Summary sheet with the Pull function isn't updateing the value of the cell. I have to edit it and after I press Enter again it updates the value instantly.
Is there something I've missed out from the Module?
I would like to update the value as I'm opening the Summary sheet.

Cheers

 

[sijpie]

You will have to make it a volatile function in order to have it update on sheet calculation:
see these links

Writing efficient VBA UDFs Part 10 – Volatile Functions and Function Arguments | Excel and UDF Performance Stuff
Volatile Method [Excel 2003 VBA Language Reference]
Excel Recalculation

 

[sabi.kiss]

Mersi mult sijpie

 

[sijpie]

cu placere!