Using VLOOKUP and INDIRECT to pull data from a variable-sized table on a different sheet

[dhowpow]

I currently have a sheet containing different categories of hours (standard, overtime, holiday, etc.) worked during a two-week payroll period by each employee at a company. I want to create separate summary sheets for each of the managers that show only their employees and the hours worked by each one. The goal here is to make it so pretty much all they need todo to the sheet is check that the column of employee names accurately reflects who is currently on their team (and Excel formulas they don't need to look at or touch will do the rest by pulling the hours data from the main sheet). The issue I am running into is that the number of shifts worked and how employees worked them varies every pay period, so instead of doing a VLOOKUP to a pre-defined table with pre-defined coordinates, I am trying to create formulas that can dynamically reflect the size of the data table without the managers needing to tweak any numbers inside formulas. I was hoping to use INDIRECT() to pull cell values to make it more friendly, so the managers only need to type in the number of the last row of data for the big raw data set each time and then that would adjust the Excel formulas each payroll.

For example: On Sheet 1, all the hours data form this pay period is contained within the table B16:O6110. Column B is the employees name, and their standard number of hours worked is in Column C. Cell S13 in the header concatenates the min and max row numbers and max column letters to create a cell that defines the values of the table range (and thus contains "B16:O6110" as a string).
On the manager summary sheet, Column B contains the name of each employee on the respective manager's team. In the cell where I am trying to pull the employee's standard hours data info over, I am trying to use this formula:

=VLOOKUP(B4,INDIRECT("’Sheet1"&"’!$S$13"),2)

but I keep getting a #REF! error. I am trying to get that formula to take the INDIRECT() part of the function as an instruction to plug the string 'Sheet1'!$S$13 and the range to search within for the VLOOKUP. I am a bit new to INDIRECT() but have gotten it to work before in simpler scenarios, but I am even newer to concatenating anything and might but be making a really stupid error in my efforts to combine the two.

Can anyone help me fix this formula or suggest a more elegant set of formulas (or overall approach) for executing this?

Synthetic Data example of Sheet 1 (first picture) and the output format for the manager's summary sheet (second screenshot) is contained in the attached pictures for reference.

Thank you in advance - this has been driving my crazy!

 

[Special-K99]

Isolate the problem.

Remove the VLOOKUP so you just end up with the value in Sheet1!S13.
It should be a range.
If it's not a range you'll get a #REF error.

What's in Sheet1!S13 ?

And change the INDIRECT to

INDIRECT("Sheet1!$S$13")

you don't need the & "Sheet1" and "!$S$13" it's ALL text

 

[Roger Govier]

HI

Use Advanced Filter instead. Much easier and much faster.
On the Manager sheet place the Heading Manager (or whatever your header title is on your master sheet) in A1
TYpe the name of the Manager required in A2
Then starting from the Manager sheet select A1:A2 >Data >Advanced Filter >Copy to another location
Enter A4 as the Destination
Enter A1:A2 as the Criteria
Select the whole range of your source data on the other sheet as the source
Press OK

This pull across just the data for those employees who have that Manager.
You can record a macro whilst doing this, and attach it to a button.
Then whenever you enter a new Manager, just press the button and the Advanced Filter will be invoked.