Forum Discussion
Struggleing to find formula, get formula worded right.
Sample of layoutHello,
So we use excel for our time sheet. Each sheet (tab) is a 2 week period. Currently we have to look through the time sheets to add time up for each job. I have been trying to use the XLOOKUP formula, with little success. It would be nice to have a sheet that you punch the job number in and it brings up all time spent on a specific job number, and also indicate if it was field or drafting time.
1st week
=XLOOKUP(Sheet1!I1,Payperiod1!A9:A29,Payperiod1!D9:J29,
(lookup value(job#), sheet column Job # column, row and column for hours)
:XLOOKUP(Sheet1!A5,Payperiod1!Payperiod1!C9:C29,))
(what to lookup-drafting/field/comps, sheet drafting/field/comps column
2nd week
=XLOOKUP(Sheet1!I1,Payperiod1!A45:A67,Payperiod1!D45:J67,:XLOOKUP(Sheet1!A5,Payperiod1!C9:C29,Payperiod1!C45:J67))
This formula only gets me one day out of 3 days time is entered, =XLOOKUP(Sheet1!I1,Payperiod1!A9:A29,Payperiod1!D9:J29). Some of our jobs can be extended a month if not years when working on plats. All sheets are named Payperiod# Any advise would help thanks.
Are you open to a redesign of the way you gather data in the first place? The image you show us is what I'd call a classic example of a spreadsheet that began as a paper-based ledger for tracking how many hours each day were worked on this that or the other. Back in those days, it made sense to track things like this as you're doing it--easier for the human responsible for compiling and summarizing to go through and scratch things off.
As you're experiencing, though, it actually makes it harder to think of a formula that can do all that sorting through multiple pages--reflecting work by different people on different days on different jobs in different places (did I miss any of the variables?)
But if you just recorded each of those things on a single tab for all employees in the firm
Date, EE id, job #, place, hours,...
You might have multiple entries for the same date if the individual worked different jobs or different places on that date...... (your single example raises more questions than it answers)
Now, admittedly, if this is a huge firm with hundreds or thousands of employees, then some other approach to gathering the raw data would be necessary. But this brings me back to the need for a more complete description of the bigger picture.
Is it actually possible for you to share, not just an image but an actual copy of the workbook (with any real names rendered anonymous), putting it on OneDrive or GoogleDrive with a link here that grants access?
