Lookup function
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 |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com