Cell Value Formula for External Worksheet data - Blonde moment

Phelony

New Member
Joined
Apr 16, 2009
Messages
29
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! :confused:

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

="'["&('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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
This should do:
="["&('Data ID List'!A2)&"]'"&('Data ID List'!B2)&"'!$C$6"

Are you really blonde?:p (just kidding)

Cheers
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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....
 
Last edited:

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
=INDIRECT("'["&('Data ID List'!A2)&"]"&('Data ID List'!B2)&"'!$C$6")
 

Phelony

New Member
Joined
Apr 16, 2009
Messages
29
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! :biggrin:

Phel x
 

Watch MrExcel Video

Forum statistics

Threads
1,132,987
Messages
5,656,255
Members
418,292
Latest member
spd87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top