Using the text from a cell as a range name in a formula
 

I would like to create a forumla that uses the text from column A to be the
source for the named range in my forumla in column B.

For example, I have a spreadsheet where coulmn A has rows each with the name
of a city (branch location). I have a seperate table of data that has named
ranges (named by city). Can I write a LOOKUP formula that uses the text in
column A to be the source of the named range inside the formula. Referring
to the text in column A would save me the effort of typing the named range in
each formula

FYI - I don't think A pivot table would work for creating this spreadsheet
because the data is exported from another application and isn't in a friendly
format.

=Lookup(indirect(A1),...)
or
=LOOKUP(E3,INDIRECT(A1),...)
etc.

Indirect function treats text as references.
Bob Umlas

"Fletch" wrote:

I would like to create a forumla that uses the text from column A to be the
source for the named range in my forumla in column B.

For example, I have a spreadsheet where coulmn A has rows each with the name
of a city (branch location). I have a seperate table of data that has named
ranges (named by city). Can I write a LOOKUP formula that uses the text in
column A to be the source of the named range inside the formula. Referring
to the text in column A would save me the effort of typing the named range in
each formula

FYI - I don't think A pivot table would work for creating this spreadsheet
because the data is exported from another application and isn't in a friendly
format.

Thanks!


"Bob Umlas, Excel MVP" wrote:

=Lookup(indirect(A1),...)
or
=LOOKUP(E3,INDIRECT(A1),...)
etc.

Indirect function treats text as references.
Bob Umlas

"Fletch" wrote:

I would like to create a forumla that uses the text from column A to be the
source for the named range in my forumla in column B.

For example, I have a spreadsheet where coulmn A has rows each with the name
of a city (branch location). I have a seperate table of data that has named
ranges (named by city). Can I write a LOOKUP formula that uses the text in
column A to be the source of the named range inside the formula. Referring
to the text in column A would save me the effort of typing the named range in
each formula

FYI - I don't think A pivot table would work for creating this spreadsheet
because the data is exported from another application and isn't in a friendly
format.

Hi!

Try this:

=VLOOKUP(lookup_value,INDIRECT(A2),2,0)

Where A2 = city_name and city_name is the named range.

Biff

"Fletch" wrote in message
...
I would like to create a forumla that uses the text from column A to be the
source for the named range in my forumla in column B.

For example, I have a spreadsheet where coulmn A has rows each with the
name
of a city (branch location). I have a seperate table of data that has
named
ranges (named by city). Can I write a LOOKUP formula that uses the text
in
column A to be the source of the named range inside the formula.
Referring
to the text in column A would save me the effort of typing the named range
in
each formula

FYI - I don't think A pivot table would work for creating this spreadsheet
because the data is exported from another application and isn't in a
friendly
format.