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

dhowpow

New Member
Joined
Dec 8, 2019
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Attachments

  • Screen Shot 2019-12-09 at 12.13.45 AM.png
    Screen Shot 2019-12-09 at 12.13.45 AM.png
    60.6 KB · Views: 11
  • Screen Shot 2019-12-09 at 12.15.32 AM.png
    Screen Shot 2019-12-09 at 12.15.32 AM.png
    26.8 KB · Views: 11

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

Active Member
Joined
Jun 19, 2002
Messages
303
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,514
Messages
5,636,785
Members
416,940
Latest member
JohanT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top