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.
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 column2nd 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. Read More
