Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup on multiple worksheets? | Excel Discussion (Misc queries) | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
VLOOKUP Function using multiple worksheets | Excel Discussion (Misc queries) | |||
VLOOKUP Function using multiple worksheets | Excel Discussion (Misc queries) | |||
vlookup over multiple worksheets | Excel Worksheet Functions |