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.