Rob Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2802539%22%20slang%3D%22en-US%22%3ERob%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802539%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20display%20a%20total%20on%20sheet1%20from%20a%20varying%20cell%20in%20sheet2%3C%2FP%3E%3CP%3EI've%20created%20a%20timesheet%20%26amp%3B%20need%20to%20display%20their%20total%20hours%20worked%20for%20the%20two%20weeks.%3C%2FP%3E%3CP%3EI%20get%20the%20week%20number%20from%20cell%20E1%20on%20sheet1%20using%20%22%3DWEEKNUM(NOW())%22%3C%2FP%3E%3CP%3EI%20then%20get%20the%20corresponding%20weeks%20row%20number%20from%20sheet2%20using%20%22XMATCH(E1%2C'C%20FORTUIN'!D%3AD%2C0)%2B19)%22%3C%2FP%3E%3CP%3EI%20now%20need%20to%20get%20the%20total%20hours%20worked%20for%20the%20current%20period%20displayed%20on%20sheet1.%3C%2FP%3E%3CP%3EHow%20do%20I%20use%20this%20info%20to%20return%20the%20total.%3C%2FP%3E%3CP%3EFormula%20%22%3D'Sheet2'!TEXTJOIN(%2C%2C%22E%22%2CXMATCH(E1%2C'Sheet2'!D%3AD%2C0)%2B19)%22%20just%20displays%20as%20text.%3C%2FP%3E%3CP%3EHave%20tried%20to%20join%20using%20%22TEXTJOIN(%2C%2C%22E%22%2CXMATCH(E1%2C'Sheet2'!D%3AD%2C0)%2B19)%22%20which%20works%20but%20is%20not%20seen%20as%20a%20formula.%3C%2FP%3E%3CP%3EAlso%20tried%20%22'Sheet2'!%24E%24XMATCH(E1%2C'Sheet2'!D%3AD%2C0)%2B19)%22%20but%20returns%20an%20error.%3C%2FP%3E%3CP%3EHELP%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2802539%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

1 Reply

@RobEurelle I believe you want to do something like this:

=INDIRECT(TEXTJOIN(,,"'C FORTUIN'!", "E",XMATCH(E1,'C FORTUIN'!D:D,0)+19))