Using cell references as text in a formula
 

I have a formula, =VLOOKUP(D1,AZ!A2:C232,3,FALSE), in a cell in sheet 1 of a
53 sheet workbook. D1 contains the name of a city. I would like to use the
value in, say D2, (a 2 letter representation of a state or territory) to be a
variable and replace the 'AZ' in the above formula. I can not figure out how
to do that or if it is even possible. I don't know if it is important but the
range will vary for each state - not the number of columns, but the number of
rows. I could make the range length equal to the worksheet with the most rows
if that would take care of the problem, or I would have to use another
variable to represent the range length.

have a look in help index for INDIRECT

--
Don Guillett
SalesAid Software

"David V" <David wrote in message
...
I have a formula, =VLOOKUP(D1,AZ!A2:C232,3,FALSE), in a cell in sheet 1 of
a
53 sheet workbook. D1 contains the name of a city. I would like to use the
value in, say D2, (a 2 letter representation of a state or territory) to
be a
variable and replace the 'AZ' in the above formula. I can not figure out
how
to do that or if it is even possible. I don't know if it is important but
the
range will vary for each state - not the number of columns, but the number
of
rows. I could make the range length equal to the worksheet with the most
rows
if that would take care of the problem, or I would have to use another
variable to represent the range length.