Vlookup Function with multiple worksheets
 

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

Vlookup Function with multiple worksheets
 

help anyone?

"Dahliahlu" wrote:

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

Vlookup Function with multiple worksheets
 

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?


--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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

Vlookup Function with multiple worksheets
 

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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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

Vlookup Function with multiple worksheets
 

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.

--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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

Vlookup Function with multiple worksheets
 

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 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.


Regards,
Ryan----

--
RyGuy


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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

Vlookup Function with multiple worksheets
 

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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 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.


Regards,
Ryan----

--
RyGuy


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Dahliahlu" wrote in message
...
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