INDIRECT, relative name as argument
 

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

INDIRECT, relative name as argument
 

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

INDIRECT, relative name as argument
 

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" wrote:

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

INDIRECT, relative name as argument
 

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

INDIRECT, relative name as argument
 

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

Mike

"Werner Rohrmoser" wrote:

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

INDIRECT, relative name as argument
 

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

INDIRECT, relative name as argument
 

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

INDIRECT, relative name as argument
 

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" wrote in message
...
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

INDIRECT, relative name as argument
 

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