INDIRECT with local named range

[NukeRiskGuy]

In Excel 2003, how come this works:

=INDEX(data!p_T6115,1,1)


but in a different worksheet, this does not work:

=INDEX(INDIRECT("data!p_T6115"),1,1)


The defined name p_T6115 refers to:

=OFFSET(data!$AK$3,0,0,COUNTA(data!$AK:$AK)-2,1)


and I have tried it with p_T6115 defines as local and global.


Thanks in advance for your response.


NukeRiskGuy

 

[SirJB7]

Hi, NukeRiskGuy!

Can you please copy the values you have in column AK rows 1 thru 10 on worksheet data? Thanks.

Regards!

 

[NukeRiskGuy]

STATOR COIL WATER TEMP

T6115@UNIT

148.50497

147.82567

147.4859

148.17952

148.16534

148.84451

148.51912

148.53325


Note that my OFFSET begins with the third row which is the 148.50497 value.

 

[Luke M]

If it's a locally defined name, then you need to include the sheet name like so:

=INDEX(INDIRECT("data!p_T6115"),1,1)


Otherwise, just the name is needed:

=INDEX(INDIRECT("p_T6115"),1,1)


If that's not working, could you better clarify what "does not work" means in this sense? (error message, wrong data, etc)


Or, perhaps you could share what the actual end goal is (since an INDIRECT function with everything written doesn't make a lot of usefulness). Could you skip using a named range and just go directly to the OFFSET function?

 

[SirJB7]

Hi, NukeRiskGuy!

First, I'm using Excel 2010.

I copied that into a worksheet called "data" in AK1:AK10. The defined range "p_T6115" address is $AK3:$AK10 with scope in the whole book, so I assume it's ok.

In the same sheet "data" I entered the formula =INDEX(data!p_t6115,1,1) and I retrieved the value of AK3, 148.50497. Still right.

I entered the same formula in worksheet "data2" and I still retrieve AK3 value. Still right.

Then I entered the second formula =INDEX(INDIRECT("data!p_T6115"),1,1) in the sheet "data" and #¡REF! error appeared. Splitting the formula, the error is in the INDIRECT, and it's correct: argument "data!p_T6115" hasn't a valid address reference, it has a value.

So, please check this and if I made any mistake explain in detail which value would you expect to obtain with those two formulae. Thanks.

Regards!

 

[NukeRiskGuy]

I have over 70 point names in Row 2 of a calculation worksheet:


T6112 T6113 ... T6115


In another worksheet (named "data"), I have the data from each of these points and have created dynamic named ranges in VBA for each point - these dynamic named ranges are in the form of p_[point name]. Note that the 1st column contains time stamps for when the point data was taken.


In my calculation worksheet, I want to perform calculations on the data from worksheet 'data'. For example:


=INDEX(data!p_T6112,ROW()-2,1) - INDEX(data!constant,ROW()-2,1)


Instead of hard-wiring in the "data!p_T6112", I would like to use something like:


=INDEX(INDIRECT("data!p_" & $D$2 & ",ROW() ...


since I have many point names. Using the above, I get a #REF! error.

 

[Luke M]

Playing around some more, it does appear that there is an issue with the INDIRECT function calling a named range defined by a formula. =(

However, what if we flip the order of the functions around? Using the named range "Flipped", defined as:

=OFFSET(INDIRECT(A2&"!$AK$3"),0,0,COUNTA(INDIRECT(A2&"!$AK:$AK"))-2,1)


Where A2 is the cell containing a sheet name we would be bringing into the INDIRECT function , you can then do this formula in the workbook:

=INDEX(Flipped,1)

To get the value you wanted.

Will this work?


EDIT: Wow, that's a lot of named ranges...any chance that could be reduced? Since, at least with your current formula goal, the formula really just needs to know which column in Data to look at, and then look at row 3.


LATER EDIT:

What about this?

=INDEX(Data!3:3,SUMPRODUCT(--ISNUMBER(SEARCH("T6"&$D$2&ROW(),$A$2:$BZ$2)),COLUMN($A$2:$BZ$2)))

 

[NukeRiskGuy]

Luke, I like your idea about using formulas to feed the INDEX function without using INDIRECT. I will play around with it some more today and let you know tomorrow what I figured out.


Thanks for your prompt response to my question!


NukeRiskGuy


P.S. I would still like to understand why Excel cannot deal with the INDIRECT function applied to a dynamic range.

 

[SirJB7]

@NukeRiskGuy

I'll take a rest while you try Luke M's idea.

Regards!


@Luke M

Very nice approach.

Regards!