How do I use Range Names listed in a VLookup table in a formula?
 

I currently have set up an array formula using the OFFSET function. As my
reference section in the formula, I want to be able to look up a range name
from a VLookup table based on what a user enters in a specific cell. For
instance, if the text "Gross Losses" corresponds to a range name titled
losses, I want the formula to return the range of cells defined as losses.
The problem is the formula does not recognize losses as the range name, but
only as the text "losses". Therefore the only thing returned in my array is
the text "losses" repeated.
This formula works as desired when I edit the formula and manually type in
the word losses, but I don't want the users to have to do that.

Any ideas?

See Excel help for INDIRECT which should help you since it covers named
ranges; like =indirect(A1) where A1 contains "losses". Breaking up "gross
losses" would be tougher - do you need to search for only one part of the
input (gross or losses)?


"Essbasedvlpr32" wrote in message
...
I currently have set up an array formula using the OFFSET function. As my
reference section in the formula, I want to be able to look up a range
name
from a VLookup table based on what a user enters in a specific cell. For
instance, if the text "Gross Losses" corresponds to a range name titled
losses, I want the formula to return the range of cells defined as losses.
The problem is the formula does not recognize losses as the range name,
but
only as the text "losses". Therefore the only thing returned in my array
is
the text "losses" repeated.
This formula works as desired when I edit the formula and manually type in
the word losses, but I don't want the users to have to do that.

Any ideas?

Hi
not really sure (sounds like INDIRECT). Could you post your non-working
formula

--
Regards
Frank Kabel
Frankfurt, Germany
"Essbasedvlpr32" schrieb im
Newsbeitrag ...
I currently have set up an array formula using the OFFSET function. As my
reference section in the formula, I want to be able to look up a range
name
from a VLookup table based on what a user enters in a specific cell. For
instance, if the text "Gross Losses" corresponds to a range name titled
losses, I want the formula to return the range of cells defined as losses.
The problem is the formula does not recognize losses as the range name,
but
only as the text "losses". Therefore the only thing returned in my array
is
the text "losses" repeated.
This formula works as desired when I edit the formula and manually type in
the word losses, but I don't want the users to have to do that.

Any ideas?

Thank you Dave R. and Frank Kabel. The INDIRECT function accomplished what I
needed. This is one piece of a bigger project so I might be posting again if
I run into more problems. I've used Excel for quite a while, but I am new to
the array formulas and the OFFSET function.

Thanks again,

Essbasedvlpr32

"Dave R." wrote:

See Excel help for INDIRECT which should help you since it covers named
ranges; like =indirect(A1) where A1 contains "losses". Breaking up "gross
losses" would be tougher - do you need to search for only one part of the
input (gross or losses)?


"Essbasedvlpr32" wrote in message
...
I currently have set up an array formula using the OFFSET function. As my
reference section in the formula, I want to be able to look up a range
name
from a VLookup table based on what a user enters in a specific cell. For
instance, if the text "Gross Losses" corresponds to a range name titled
losses, I want the formula to return the range of cells defined as losses.
The problem is the formula does not recognize losses as the range name,
but
only as the text "losses". Therefore the only thing returned in my array
is
the text "losses" repeated.
This formula works as desired when I edit the formula and manually type in
the word losses, but I don't want the users to have to do that.

Any ideas?