Look up date, pull result from another column, and populate it in another sheet?

I'm trying to figure out how to look up 'yesterday' for each of the employees' spreadsheets, find the corresponding goal numbers, and populate them on the Leaderboard sheet. For example, on February 12th Bob was 4 tires ahead of his goal (February 11th shows 4) and I want that to populate on the Leaderboard sheet.

Technically the goal is 2 tires/day with a 5 day week, but some employees work Tues-Sat and some are Mon-Fri, so I think I have to make the goal 10/wk instead of 2/day. That will make the daily goal a tad off depending on the day, but I think it's something we have to live with.

I'm not sure I'm fully understanding how your workbook operates or what you're looking for, but hopefully I'm close enough to help. You can use the following formula in G5 of the leaderboard to return each employee's total as of yesterday:

=SUM(OFFSET(INDIRECT($A5&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A5&"!$C$2:$C$25"),1)))

You can fill it down through G9 to get the totals for the rest of the team. If you want to compare those totals to the goal of 10 per seven days, you can use:

=SUM(OFFSET(INDIRECT($A5&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A5&"!$C$2:$C$25"),1)))-(DAY(TODAY()-1)*10/7)

instead of the earlier formula. I put those results in column L of my attachment so you could take a look. There are likely easier, more accurate approaches, but it requires a better understanding of what your table is doing. In particular, there are columns on BOB that don't appear on other sheets, some sheets are missing February 12th, and it's not quite clear how you're filling in the data. If you're adding their tire totals each day, then the leaderboard should be able to refer to I26 of sheet for the current total, correct? That could simplify the formulas above. Additionally, when an employee doesn't work, do you enter a zero for that day, or leave it blank? If it's left blank or marked with an "x" or somehow identified in column I, we could craft a formula that uses 2/day instead of 10/week as an expectation. Take a look at the attachment and let me know what you think:

Originally Posted by CAntosh

I'm not sure I'm fully understanding how your workbook operates or what you're looking for, but hopefully I'm close enough to help. You can use the following formula in G5 of the leaderboard to return each employee's total as of yesterday:

=SUM(OFFSET(INDIRECT($A5&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A5&"!$C$2:$C$25"),1)))

You can fill it down through G9 to get the totals for the rest of the team. If you want to compare those totals to the goal of 10 per seven days, you can use:

=SUM(OFFSET(INDIRECT($A5&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A5&"!$C$2:$C$25"),1)))-(DAY(TODAY()-1)*10/7)

instead of the earlier formula. I put those results in column L of my attachment so you could take a look. There are likely easier, more accurate approaches, but it requires a better understanding of what your table is doing. In particular, there are columns on BOB that don't appear on other sheets, some sheets are missing February 12th, and it's not quite clear how you're filling in the data. If you're adding their tire totals each day, then the leaderboard should be able to refer to I26 of sheet for the current total, correct? That could simplify the formulas above. Additionally, when an employee doesn't work, do you enter a zero for that day, or leave it blank? If it's left blank or marked with an "x" or somehow identified in column I, we could craft a formula that uses 2/day instead of 10/week as an expectation. Take a look at the attachment and let me know what you think:

THANK YOU! The second formula is what I'm looking for.

Sorry I didn't explain things more clearly. To answer your questions, I added columns to Bob's sheet to play around with comparing the total sold to the goal. That's why he has extra columns. But then I wasn't sure how to set up a formula on the Leaderboard sheet. The person who started the sheet had originally manually deleted Sundays from the dates list, but that's obviously labor intensive to do each month, and the manager will still be filling in a 0 for each employee one day each week because they work 5 out of the 6 days the store is open (either M-F or Tu-Sat.). This will be updated daily. If we could leave it blank (or fill in an x) that would be better if we could change the formula to 2/day so the numbers aren't off slightly depending on which day of the week it is (ie if we add 1.4/day 7 days a week instead of 2/day only on the days they work).

Hopefully that answers your questions. Thanks again for your help!!!

My pleasure! If you use "x" for unworked days, then you can use the formula below to get the difference between each employee's tires sold and his/her goal, calculated at 2 per day worked:

=SUM(OFFSET(INDIRECT($A8&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A8&"!$C$2:$C$25"),1)))-2*COUNTIFS(OFFSET(INDIRECT($A8&"!I2"),0,0,MATCH(TODAY()-1,INDIRECT($A8&"!$C$2:$C$25"),1)),"<>x")

This formula will produce the difference as of yesterday. It's in L5:L9 of the attachment. I added some fake data for Tom and John to test it out.

That's it!!! I was able to adjust the formula for the 3 other goal columns, and adjust it for 3/day for the last column. You totally made my day. Thanks!