Vlookup Function with multiple worksheets

[Dahliahlu]

Hi guys,

I've been looking through the past discussions but could not find the answer
to my problem.

I have a document with several worksheets.
There are individual wkshts organized by location. They have the
location/professor and all the other info in them.

Then there are two summary wkshts, 1 organized by location, the other by
professor.


I am looking for the easiest way to keep the summary pages updated
automatically and right now,
I have a vlookup function on the wksht organized by professor.

Is there any way for me to be able to input a formula that would
allow me to search multiple worksheets for a text value "professor name"
and input the corresponding location and etc. under it?

Thanks guys

 

[Dahliahlu]

help anyone?

 

[T. Valko]

Not enough detail.

You want to perform a lookup on several sheets for a name.

Ok, now fill in the details. This is the kind of info we would need to make
a suggestion:

How many sheets? What are the sheet names? Are they random names (Miami,
Pittsburgh, Buffalo) or do they have some kind of sequential pattern
(Region1, Region2, Region3)? Do all these sheets have the same layout? (this
would be *required* for a lookup to work). Where would we find the name on
these sheets? Might a name appear on more than one sheet? Once we find the
name, then what? Where is the data located that you want to return?

 

[Dahliahlu]

Hey Biff,

I have a workbook with multiple wkshts.
Each wksht is titled as an invidual class and has the corresponding
information in it such as attendees, professor, location, date

I want to have 2 summary pages. 1 that organizes the data in the previous
wkshts by class and 1 that organizes the data by location (they are repeated
so there are multiple instances of Houston etc.).


I want these summary pages to be automatic so I wont have to manually type
in the data each time and only have to input data into the separate class
wkshts.


There are 9 tabs each labeled with the title of the class.
A name does appear on more than one sheet.
All the sheets do have the same layout.

The title of the class is located in A1.
The location of the class is located in A4:A10.

The data I want to return is located on to the left of the location. For
example,
A4 is Houston.
Then B4,C4,D4 all have the information including professor, date,
attendance% etc.

It is from B4 to J4 on each wksht.

Thanks!

 

[T. Valko]

Well, what you want to do is not easy!

I don't think you can do this the way you want with formulas. It "could" be
done but there would be empty rows of data on your summary sheets and I'm
pretty sure that is not something you want.

I suggest that you post this in the programming forum since a VBA solution
would be the best way to go.

 

[ryguy7272]

Give this a shot:
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 Insert>Module
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.


Regards,
Ryan----

 

[T. Valko]

You can use a worksheet formula to lookup across multiple sheets.

The problem in this situation is that there are multiple instances of the
lookup value on more than one sheet.

So, I don't think a standard lookup (either the worksheet formula or the VBA
equivalent function below) is going to work in this instance.