I am using the following lookup function to return a value. I am currently
using one sheet for the values. I need to be able to use a number of sheets
representing different counties and values. The formula looks like this
=VLOOKUP(F12,Sheet1!B2:N53,2)

I would like to be able to vary the Sheet1 to be different counties and
equal a cell where the user would imput the county name. The sheets would be
named the same.

Can this be done??

Jeff

Give the range Sheet1!B2:N53 the name of the county, e.g., "Cuyahoga"

Then, if the word Cuyahoga is in cell A1, use

=VLOOKUP(F12,INDIRECT(A1),2)


"jsteele" wrote:

I am using the following lookup function to return a value. I am currently
using one sheet for the values. I need to be able to use a number of sheets
representing different counties and values. The formula looks like this
=VLOOKUP(F12,Sheet1!B2:N53,2)

I would like to be able to vary the Sheet1 to be different counties and
equal a cell where the user would imput the county name. The sheets would be
named the same.

Can this be done??

Jeff

Try this...


=VLOOKUP(F12,Indirect($B$1),2,0)

In cell B1, you can enter the country name such as France.


Also, for each country, go to the respective sheet, select this range
B2:N53, and name the country...France, Greece, Italy, etc. Populating
cell B1 with the same name.


--
Rlmccants
------------------------------------------------------------------------
Rlmccants's Profile: http://www.excelforum.com/member.php...o&userid=26097
View this thread: http://www.excelforum.com/showthread...hreadid=395131

Thanks, I will try and get back to you.

Jeff

"Duke Carey" wrote:

Give the range Sheet1!B2:N53 the name of the county, e.g., "Cuyahoga"

Then, if the word Cuyahoga is in cell A1, use

=VLOOKUP(F12,INDIRECT(A1),2)


"jsteele" wrote:

I am using the following lookup function to return a value. I am currently
using one sheet for the values. I need to be able to use a number of sheets
representing different counties and values. The formula looks like this
=VLOOKUP(F12,Sheet1!B2:N53,2)

I would like to be able to vary the Sheet1 to be different counties and
equal a cell where the user would imput the county name. The sheets would be
named the same.

Can this be done??

Jeff

This is getting me closer. The problem is that there is multiple ranges on
each sheet that i need to access. Is there a way to continue using the range
ie, B2:N53 for this function and then other for different functions but have
the sheet name only change by entering it into a cell on the main page.

jeff

"Rlmccants" wrote:


Try this...


=VLOOKUP(F12,Indirect($B$1),2,0)

In cell B1, you can enter the country name such as France.


Also, for each country, go to the respective sheet, select this range
B2:N53, and name the country...France, Greece, Italy, etc. Populating
cell B1 with the same name.


--
Rlmccants
------------------------------------------------------------------------
Rlmccants's Profile: http://www.excelforum.com/member.php...o&userid=26097
View this thread: http://www.excelforum.com/showthread...hreadid=395131

I have tried your advise and found that the problem is solved for that
paticular range but will not solve my problem for other ranges on the same
county sheet. Is there a way to make just the sheet name variable. I thought
there might be a way to imbed a function inside a function but have been not
be able to get it to work correctly. Do you have any other suggestions.

Thank you in advance
Jsteele

"jsteele" wrote:

This is getting me closer. The problem is that there is multiple ranges on
each sheet that i need to access. Is there a way to continue using the range
ie, B2:N53 for this function and then other for different functions but have
the sheet name only change by entering it into a cell on the main page.

jeff

"Rlmccants" wrote:


Try this...


=VLOOKUP(F12,Indirect($B$1),2,0)

In cell B1, you can enter the country name such as France.


Also, for each country, go to the respective sheet, select this range
B2:N53, and name the country...France, Greece, Italy, etc. Populating
cell B1 with the same name.


--
Rlmccants
------------------------------------------------------------------------
Rlmccants's Profile: http://www.excelforum.com/member.php...o&userid=26097
View this thread: http://www.excelforum.com/showthread...hreadid=395131