Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets?
I'm looking to search multiple worksheets labeld by date using the vlookup
function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets?
This is from a post (verbatim) a while back:
The Custom Excel Functions Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num as Integer, Optional Range_look as Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid.com 'Use VLOOKUP to Look across ALL Worksheets and stops _ at the first match found. ''''''''''''''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet Dim vFound On Error Resume Next For Each wSheet In ActiveWorkbook.Worksheets With wSheet Set Tble_Array = .Range(Tble_Array.Address) vFound = WorksheetFunction.VLookup _ (Look_Value, Tble_Array, _ Col_num, Range_look) End With If Not IsEmpty(vFound) Then Exit For Next wSheet Set Tble_Array = Nothing VLOOKAllSheets = vFound End Function To use this code do this: 1. Push Alt+F11 and go to InsertModule 2. Copy and paste in the code. 3. Push Alt+Q and Save. Now in any cell put in the Function like this: =VLOOKAllSheets("Dog",C1:E20,2,FALSE) Where "Dog" is the value to find " " C1:E20 is the range to look in the first column and find "Dog" " " 2 is the relative column position in C1:E20 to return return our result from. " " FALSE (or ommited) means find and exact match of "Dog" In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. Hope that helps you a bit. Ryan-- -- RyGuy "LDP Analyst" wrote: I'm looking to search multiple worksheets labeld by date using the vlookup function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets?
You can use INDIRECT or CHOOSE.
Personally I prefer to use CHOOSE wherever possible (relatively small number of worksheets) because its faster. You need to have a way of converting the date in your dropdown to a number (sheetnumber) then use something like this: =VLOOKUP(lookval,CHOOSE(Sheetnumber, Range1, Range2, Range3, ....Rangen),colnum) alternatively you could use INDIRECT something like this assuming that cell D99 contains the sheet name resulting from the dropdown and the lookup range is A2:Z100. =VLOOKUP(lookval,INDIRECT(D99 & "!A2:Z100"),colnum) Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "LDP Analyst" <LDP wrote in message ... I'm looking to search multiple worksheets labeld by date using the vlookup function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets?
Be careful with that function: it will not recalculate correctly when
something in the Tble_Array on a different worksheet is changed, unless you make it Volatile. regards Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "ryguy7272" wrote in message ... This is from a post (verbatim) a while back: The Custom Excel Functions Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num as Integer, Optional Range_look as Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid.com 'Use VLOOKUP to Look across ALL Worksheets and stops _ at the first match found. ''''''''''''''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet Dim vFound On Error Resume Next For Each wSheet In ActiveWorkbook.Worksheets With wSheet Set Tble_Array = .Range(Tble_Array.Address) vFound = WorksheetFunction.VLookup _ (Look_Value, Tble_Array, _ Col_num, Range_look) End With If Not IsEmpty(vFound) Then Exit For Next wSheet Set Tble_Array = Nothing VLOOKAllSheets = vFound End Function To use this code do this: 1. Push Alt+F11 and go to InsertModule 2. Copy and paste in the code. 3. Push Alt+Q and Save. Now in any cell put in the Function like this: =VLOOKAllSheets("Dog",C1:E20,2,FALSE) Where "Dog" is the value to find " " C1:E20 is the range to look in the first column and find "Dog" " " 2 is the relative column position in C1:E20 to return return our result from. " " FALSE (or ommited) means find and exact match of "Dog" In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. Hope that helps you a bit. Ryan-- -- RyGuy "LDP Analyst" wrote: I'm looking to search multiple worksheets labeld by date using the vlookup function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets
I was heading down the INDIRECT path when I decided to hide another data
chart that used HLOOKUP off of the drop down and the data chart that used VLOOKUP. I made a graph chart from the HLOOKUP values since those values change based off of the VLOOKUP from the other two drop downs. Maybe an indirect method of getting there, but it solved the problem for me. LDP Analyst "Charles Williams" wrote: You can use INDIRECT or CHOOSE. Personally I prefer to use CHOOSE wherever possible (relatively small number of worksheets) because its faster. You need to have a way of converting the date in your dropdown to a number (sheetnumber) then use something like this: =VLOOKUP(lookval,CHOOSE(Sheetnumber, Range1, Range2, Range3, ....Rangen),colnum) alternatively you could use INDIRECT something like this assuming that cell D99 contains the sheet name resulting from the dropdown and the lookup range is A2:Z100. =VLOOKUP(lookval,INDIRECT(D99 & "!A2:Z100"),colnum) Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "LDP Analyst" <LDP wrote in message ... I'm looking to search multiple worksheets labeld by date using the vlookup function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
choose table_array in VLOOKUP from list of multiple worksheets?
On Jan 21, 10:15*am, ryguy7272
wrote: This is from a post (verbatim) a while back: The Custom Excel Functions Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ * * * * * * * * * * * * * Col_num as Integer, Optional Range_look as Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid.com 'Use VLOOKUP to Look across ALL Worksheets and stops _ at the first match found. ''''''''''''''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet Dim vFound On Error Resume Next *For Each wSheet In ActiveWorkbook.Worksheets * With wSheet * Set Tble_Array = .Range(Tble_Array.Address) * *vFound = WorksheetFunction.VLookup _ * *(Look_Value, Tble_Array, _ * *Col_num, Range_look) * End With * If Not IsEmpty(vFound) Then Exit For *Next wSheet *Set Tble_Array = Nothing *VLOOKAllSheets = vFound End Function To use this code do this: 1. Push Alt+F11 and go to InsertModule 2. Copy and paste in the code. 3. Push Alt+Q and Save. Now in any cell put in the Function like this: =VLOOKAllSheets("Dog",C1:E20,2,FALSE) Where "Dog" is the value to find " " C1:E20 is the range to look in the first column and find "Dog" " " 2 is the relative column position in C1:E20 to return return our result from. " " FALSE (or ommited) means find and exact match of "Dog" In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. Hope that helps you a bit. Ryan-- -- RyGuy "LDP Analyst" wrote: I'm looking to search multiple worksheets labeld by date using the vlookup function. I'm using the drop down, and I want to know if there is a way to reference the drop down to a list of ranges, or have that drop down be a changing reference to in the table_array section of the vlookup function..- Hide quoted text - - Show quoted text - I've tried to add this macro, yet get an error message in the formula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup table_array | Excel Discussion (Misc queries) | |||
vlookup with dynamic table_array | Excel Worksheet Functions | |||
Using VLOOKUP with abitlity to choose from multiple defined names. | Excel Worksheet Functions | |||
VLOOKUP TABLE_ARRAY | Excel Worksheet Functions | |||
vlookup template -> table_array | Excel Worksheet Functions |