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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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,343
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,343
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,343
=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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,411
Messages
5,511,180
Members
408,829
Latest member
sheshe123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top