Help With Formula to Search 30+ Worksheets to match Info in Cell A1, then show information

Hi All!

I am having a lot of problems with a spreadsheet I am trying to create. I am attempting to do a LOOKUP for a phrase in cell A1 (have a drop down of 30 location names).

After the user pulls down their location name, I want it to "pull" information from one of the 30 worksheets that has that specific location information.

For instance:

User pulls down "Texas" location -> function searches for "Texas" in 30 workbooks. Identifies the workbook that has "Texas" in it, then displays the employees and their information on the main page.

The columns in each of the 30 workbooks are static, they contain sales objectives.. The rows are variable, each location has varying number of employees.

The locations could be manually made for the sheet name, the data source populates the location name in cell A1 for every worksheet.

Originally Posted by NBVC

If the sheetname is Texas, and you want to go to that sheet and pull the info, then yes, you can simply refer to the sheet using the INDIRECT function.... not necessarily needing a macro.

So, if the sheet names were the same as the 30 locations in the drop down, when you pulled down one of the locations, the INDIRECT function would validate one of the 30 sheets with the name of the location in the drop down?

One issue I am foreseeing from the link you sent, is that each location has a variable # of employees. Anywhere from 5-40. So I can't set it to look in a specific # of rows. Ideally it would copy and paste the rows which have the employee names, and then their sales performance under each column header.. which are static.

Originally Posted by NBVC

If the sheetname is Texas, and you want to go to that sheet and pull the info, then yes, you can simply refer to the sheet using the INDIRECT function.... not necessarily needing a macro.

I am sorry for what may be a dumb question, but looking up the INDIRECT macro I did not see where it could reference an entire workbook by name. I also don't quite grasp how to choose the rows following that.

One thing that every workbook does have is this:

"TOTAL"
-employee
-employee
"SUMMARY"

Would it be possible to use an INDIRECT function that tells the worksheet to grab all data from rows between "TOTAL" and "SUMMARY"?