Oct 01 2021 04:57 AM
I need a formula to display a total on sheet1 from a varying cell in sheet2
I've created a timesheet & need to display their total hours worked for the two weeks.
I get the week number from cell E1 on sheet1 using "=WEEKNUM(NOW())"
I then get the corresponding weeks row number from sheet2 using "XMATCH(E1,'C FORTUIN'!D:D,0)+19)"
I now need to get the total hours worked for the current period displayed on sheet1.
How do I use this info to return the total.
Formula "='Sheet2'!TEXTJOIN(,,"E",XMATCH(E1,'Sheet2'!D:D,0)+19)" just displays as text.
Have tried to join using "TEXTJOIN(,,"E",XMATCH(E1,'Sheet2'!D:D,0)+19)" which works but is not seen as a formula.
Also tried "'Sheet2'!$E$XMATCH(E1,'Sheet2'!D:D,0)+19)" but returns an error.
HELP
Oct 01 2021 05:20 AM
@RobEurelle I believe you want to do something like this:
=INDIRECT(TEXTJOIN(,,"'C FORTUIN'!", "E",XMATCH(E1,'C FORTUIN'!D:D,0)+19))