Cell Value Formula for External Worksheet data - Blonde moment

[Phelony]

Hi guys

I seem to be having a blonde moment. The below formula is returning it's own value and not the value of the cell it's directed at. I've tried several fixes for this now and don't seem to be able to identify what's going wrong!

Could anyone offer a suggestion? I think my brain has failed on this.

="'["&('Data ID List'!A2)&"]"&('Data ID List'!B2)&"'!$C$6"

The references in the Data ID List should direct the formula to pick up the values in a specific workbook and then a specific worksheet, but instead it's returning the correct address but not the value at the other end of it....

I'm stuck!

Any help would be appreciated, I suspect this is a really stupid error on my part.

Phel x

 

[Andrew Poulsom]

To build a formula like that you need to use INDIRECT. But that will only work if the source workbook is open. If the workbook is closed you can use the INDIRECT.EXT function from the MoreFunc Add-In.

 

[bobsan42]

This should do:
="["&('Data ID List'!A2)&"]'"&('Data ID List'!B2)&"'!$C$6"

Are you really blonde? (just kidding)

Cheers

 

[bobsan42]

To build a formula like that you need to use INDIRECT. But that will only work if the source workbook is open. If the workbook is closed you can use the INDIRECT.EXT function from the MoreFunc Add-In.

You are absolutely right Andrew but (s)he(?) (Phelony) may only want to build the refernce and use it later in another formula/function.

oh no - you're twice right

but instead it's returning the correct address but not the value at the other end of it....

 

[bobsan42]

=INDIRECT("'["&('Data ID List'!A2)&"]"&('Data ID List'!B2)&"'!$C$6")

 

[Phelony]

lol, not actually, just for a moment!

Thank you so much for that. I generally avoid using indirect because of the open workbook requirement, but for the number of workbooks that I'm going to have to extract data from I can write a quick Macro to run through them as the Data ID List table identifies newly returned workbooks.

Thank you gentlemen for your swift and very helpful answers!

Phel x