INDIRECT, relative name as argument

[Werner Rohrmoser]

The name (string) of the relative name (e.g "TestName") is written in
Range "A1".
TestName itself is a named formula like "= SheetRef!A$3".
Purpose is to get always the value in row three of the correspondent
column.
When I enter "=TestName" in Range "F26" i will get the value of Range
"F3".
So, and now I'd like to use a formula, where I use the relative name,
which is entered
in Range "A1", something like =INDIRECT($A$1), but this doesn't work.
The idea is to use the values in column "A" for the relative names in
the INDIRECT function.

Any approach for a solution?

Regards
Werner

 

[Bernd P]

Hello Werner,

What exactly are you doing?

If you enter into A1
testname

Into C1
3812

Into E1
=INDIRECT(A1)

Then you will get as a result in E1
3812
if testname has been defined as a name with value
=Sheet1!C1

Regards,
Bernd

 

[Mike H]

Hi,

I don't understand the problem because what you have described works.

Tou have a named range "TestName" that holds a formula =Sheet3!A1
You write the name of that range in another cell (A1)
the formula

=indirect(A1)

evaluates the formula in "TestName" and returns the value from sheet 3 A1

Mike

 

[Werner Rohrmoser]

Hi Bernd,

is your name really defined as: =Sheet1!C1 or as Sheet1!$C$1?
When you use mixed relative or relative names it doesn't work and
this is my problem.

Regards,
Werner

 

[Mike H]

As in my previous post that still works perfectly for me independant of
whether the ranges are referenced absolutely.

Mike

 

[Werner Rohrmoser]

Hi Mike,

difficult to explain, I'm going to try my best, I'm not a native
speaker.

See below row 1 to 3
In row 2 I have the string "TestName" in col A and then the formula to
the right in all columns.
In row 3 once more the same INDIRECT formula.

A B C D E F G
TestName A A A A
A A <= INDIRECT($A$2)
A <= INDIRECT($A$2)

TestName defined as: =Sheet2!A$1 (column is relative)

Regards
Werner

 

[Werner Rohrmoser]

Mike,

the named formula is referenced relative, not the reference for the
INDIRECT function.
When you do that you will get 0 instead of 3812.

Regards
Werner

 

[Charles Williams]

I think implicit intersection does what you want:

define TestName as =SheetRef!$A$3:$IV$3

put TestName in cell A2

then =INDIRECT(A2) will give you the intersect of whatever column the
INDIRECT is in and SheetRef row 3


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Werner Rohrmoser]

Hi Charles,

yes that's it!
It was really hard to explain what I was looking for.
Great, it's not the first time that I learned something about names
and
relative references fom you.
Thank you very much!

Regards
Werner