Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
trempnvt
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roly
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
trempnvt
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Larry S
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
trempnvt
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use a cell value to reference a worksheet name S2 Excel Worksheet Functions 2 October 10th 05 03:02 PM
dynamic worksheet reference bmccall17 Excel Worksheet Functions 1 September 19th 05 06:46 PM
Reference the worksheet from a multiple worksheet range function ( DBickel Excel Worksheet Functions 1 May 28th 05 03:49 AM
Absolute Worksheet reference number Tony M Excel Discussion (Misc queries) 4 March 21st 05 07:10 PM
make a vlookup using a variable path Alex St-Pierre Excel Worksheet Functions 1 March 3rd 05 12:54 AM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"