Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
Hello, I'm trying to run a vlookup where the worksheet (within the file) referenced would depend on the value of a certain cell. For example, if B5 could be a number different fruits, and there were different worksheets named after the fruit options, I could have one cell where I would look up something on worksheet 'Apple' if B5="Apple" or on worksheet 'Grape' if B5="Grape". When B5="Apple", =VLOOKUP(A2,'Apple'!A1:C37,3,FALSE) will give me the correct value, but both =VLOOKUP(A2,B5!A1:C37,3,FALSE) and =VLOOKUP(A2,'B5'!A1:C37,3,FALSE) will open a browse window and ask me to update the values for B5. Any help would be much appreciated. -- trempnvt ------------------------------------------------------------------------ trempnvt's Profile: http://www.excelforum.com/member.php...o&userid=34710 View this thread: http://www.excelforum.com/showthread...hreadid=544779 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
Hi, How many criteria are there in the list? If there only a few, you could put it in an IF statement. All the best, Roly -- roly ------------------------------------------------------------------------ roly's Profile: http://www.excelforum.com/member.php...o&userid=24631 View this thread: http://www.excelforum.com/showthread...hreadid=544779 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
roly Wrote: How many criteria are there in the list? If there only a few, you could put it in an IF statement. Eighteen. I could do it that way or by naming the ranges that I want to select from, but if there's a cleaner way to do it, that's my preference. Thanks for your help! -- trempnvt ------------------------------------------------------------------------ trempnvt's Profile: http://www.excelforum.com/member.php...o&userid=34710 View this thread: http://www.excelforum.com/showthread...hreadid=544779 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
From your example below, I will assume column B will contain the COMPLETE
name of the corresponding worksheet you want to pull the data from. then in your VLOOKUP formula below, replace 'Apple'!A1:C37 with INDIRECT(CONCATENATE("'",B5,"'!A1:C37")) The CONCATENATE works like the "&" in that it is building a text string, in the case of peaches 'PEACHES'!A1:C37 and the INDIRECT allows you to addressess the cell range. Hope this helps. "trempnvt" wrote in message ... Hello, I'm trying to run a vlookup where the worksheet (within the file) referenced would depend on the value of a certain cell. For example, if B5 could be a number different fruits, and there were different worksheets named after the fruit options, I could have one cell where I would look up something on worksheet 'Apple' if B5="Apple" or on worksheet 'Grape' if B5="Grape". When B5="Apple", =VLOOKUP(A2,'Apple'!A1:C37,3,FALSE) will give me the correct value, but both =VLOOKUP(A2,B5!A1:C37,3,FALSE) and =VLOOKUP(A2,'B5'!A1:C37,3,FALSE) will open a browse window and ask me to update the values for B5. Any help would be much appreciated. -- trempnvt ------------------------------------------------------------------------ trempnvt's Profile: http://www.excelforum.com/member.php...o&userid=34710 View this thread: http://www.excelforum.com/showthread...hreadid=544779 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
Think we could use INDIRECT in the vlookup, viz.:
=VLOOKUP(A2,INDIRECT("'"&B5&"'!A1:C37"),3,FALSE) where B5 houses the sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
Thank you all for your help! The INDIRECT trick worked. -- trempnvt ------------------------------------------------------------------------ trempnvt's Profile: http://www.excelforum.com/member.php...o&userid=34710 View this thread: http://www.excelforum.com/showthread...hreadid=544779 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with variable worksheet reference
"trempnvt" wrote:
Thank you all for your help! The INDIRECT trick worked. Glad it worked for you ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use a cell value to reference a worksheet name | Excel Worksheet Functions | |||
dynamic worksheet reference | Excel Worksheet Functions | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
make a vlookup using a variable path | Excel Worksheet Functions |