Vlookup, changing table_array name

[hindsight]

I have information about identical assets from several different bank
(rates, length of term, description, etc.). I summarize all th
information on a ‘data worksheet’ and name each range of informatio
accordingly – Bank of America, GE Capital, First Union, Wells Fargo
etc.

I have a worksheet in the same workbook named for each bank as well an
the appropriate range of data is linked to each sheet. I use a vlooku
function to pull the appropriate to data from the ‘data worksheet.’

My question is, how can I change the table_array name in the vlooku
function without doing it manually? The way I do it now is, I go int
each worksheet select all the cells with the vlookup functions I wan
to change and do a [for example] ‘find’ Bank_of_America and replac
with ‘First_Union’, is there another way to do this?

Ideally, I’d like to type the name of the range I want in a particula
cell [for example – cell b3 of each worksheet would contain th
appropriate range name] and the table_array value in each vlooku
function on that sheet would change. Can this be done?


I want to change =vlookup(1,Bank_of_America,3,0)
To =vlookup(1,GE_Capital,3,0)

Without doing it manually. I’d like to make the ‘GE_Capital’ o
‘Bank_of_America’ linked to some other cell that I could change.

Thanks

 

[Dave Peterson]

=VLOOKUP(1,INDIRECT(b3),3,0)

might do it for you.

I have information about identical assets from several different banks
(rates, length of term, description, etc.). I summarize all the
information on a ‘data worksheet’ and name each range of information
accordingly – Bank of America, GE Capital, First Union, Wells Fargo,
etc.

I have a worksheet in the same workbook named for each bank as well and
the appropriate range of data is linked to each sheet. I use a vlookup
function to pull the appropriate to data from the ‘data worksheet.’

My question is, how can I change the table_array name in the vlookup
function without doing it manually? The way I do it now is, I go into
each worksheet select all the cells with the vlookup functions I want
to change and do a [for example] ‘find’ Bank_of_America and replace
with ‘First_Union’, is there another way to do this?

Ideally, I’d like to type the name of the range I want in a particular
cell [for example – cell b3 of each worksheet would contain the
appropriate range name] and the table_array value in each vlookup
function on that sheet would change. Can this be done?

I want to change =vlookup(1,Bank_of_America,3,0)
To =vlookup(1,GE_Capital,3,0)

Without doing it manually. I’d like to make the ‘GE_Capital’ or
‘Bank_of_America’ linked to some other cell that I could change.

Thanks,

 

[hindsight]

Dave,

Thank you, thank you, thank you. I tried several other 'fixes' but t
no avail. The INDIRECT works perfectly.

A wary cube-warrior extends a hearty muchas gracias.

J