I have a multi-sheet spreadsheet and am trying to pick up a named field
(StageName_1 .... n) that is defined on the OverallInfo worksheet. The
numeric suffix depends upon the value of the contents of the Milestone
it is related to

StageName_1 is a named field OverallInfo, E7
StageName_2 is a named field OverallInfo, E17
StageName_3 is a named field OverallInfo, E27

A30 = 100
A57 = 200
A84 = 300 etc

I ended up with the formula

=Indirect(Concatenate("StageName_",(A30/100)

But all I end up with is #REF. I countered this by using
If(IsError(Indirect etc, but that just resulted in a blank or empty
cell instead of the value of StageName_1/2/3 etc.

What am I doing wrong ?

Cheers
Fred

Hello Fred,

You did nothing wrong. It should work.

What do you get if you enter the formula editor, mark the formula part
A30/100 and push F9? Exactly 1?

Regards,
Bernd

Maybe try

=Indirect(Concatenate("StageName_",ROUND(A30/100,0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fred" wrote in message
oups.com...
I have a multi-sheet spreadsheet and am trying to pick up a named field
(StageName_1 .... n) that is defined on the OverallInfo worksheet. The
numeric suffix depends upon the value of the contents of the Milestone
it is related to

StageName_1 is a named field OverallInfo, E7
StageName_2 is a named field OverallInfo, E17
StageName_3 is a named field OverallInfo, E27

A30 = 100
A57 = 200
A84 = 300 etc

I ended up with the formula

=Indirect(Concatenate("StageName_",(A30/100)

But all I end up with is #REF. I countered this by using
If(IsError(Indirect etc, but that just resulted in a blank or empty
cell instead of the value of StageName_1/2/3 etc.

What am I doing wrong ?

Cheers
Fred

=Indirect(Concatenate("StageName_",(A30/100)

This can't be the formula you used, because it's missing
two closing parentheses. It would be easier to help you if
posted the exact formula (copied from Excel).

Assume the missing parens at the end, it works for me: do
you get the expected result if you enter =StageName_1 in
the cell?

As has been suggested elsewhere, it might be wise to
round the A30/100 calculation, in case A30 is not exactly
100 but just displayed that way.

Incidentally, it's a lot less typing to use the ampersand
operator instead of the CONATENATE function:

=INDIRECT("StageName_"&(A30/100))


hth
Andrew Taylor



Fred wrote:
I have a multi-sheet spreadsheet and am trying to pick up a named field
(StageName_1 .... n) that is defined on the OverallInfo worksheet. The
numeric suffix depends upon the value of the contents of the Milestone
it is related to

StageName_1 is a named field OverallInfo, E7
StageName_2 is a named field OverallInfo, E17
StageName_3 is a named field OverallInfo, E27

A30 = 100
A57 = 200
A84 = 300 etc

I ended up with the formula

=Indirect(Concatenate("StageName_",(A30/100)

But all I end up with is #REF. I countered this by using
If(IsError(Indirect etc, but that just resulted in a blank or empty
cell instead of the value of StageName_1/2/3 etc.

What am I doing wrong ?

Cheers
Fred