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

[Fletch]

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.

[Bob Umlas, Excel MVP]

=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.

[Fletch]

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.

[Biff]

Hi!

Try this:

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

Where A2 = city_name and city_name is the named range.

Biff

"Fletch" <Fletch@discussions.microsoft.com> wrote in message
news:AB85FD87-7A32-45EE-8164-330A84524D7A@microsoft.com...
>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.