SOLVED

Relative Cell Reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1504539%22%20slang%3D%22en-US%22%3ERelative%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504539%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20TEXTJOIN%20function%20I%20am%20trying%20to%20reference%20a%20cell%20in%20another%20sheet%20that%20has%20a%20fixed%20column%20but%20the%20row%20number%20to%20be%20used%20is%20in%20a%20cell%20in%20the%20sheet.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504539%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

In the TEXTJOIN function I am trying to reference a cell in another sheet that has a fixed column but the row number to be used is in a cell in the sheet. 

6 Replies
Highlighted

@gillisrj , it would be helpful to see a sample. Can you upload after stripping out any confidential information?

Highlighted

@gillisrj 

As @TheAntony suggested sample file is much better illustrates the task.

 

In general, if you'd like to have value from column B in sheet2 in the row which is defined by value in cell A1 of the current sheet, that could be

=INDEX(Sheet2!$B:$B,A1)
Highlighted

Under SUMMARY cell B8 you have the result for the TEXTJOIN where I specify the absolute cell reference (B8 & C8) in OFFENDER in the function. What I want to do is reference fixed columns B & C in OFFENDER but whatever row is the value contain in SUMMARY E3.@Sergei Baklan 

Highlighted
Best Response confirmed by gillisrj (New Contributor)
Solution

@gillisrj , see if this formula work for you (cell C8 in SUMMARY):

 

=INDEX(OFFENDER!$B:$B,SUMMARY!$E$3) & ", " & INDEX(OFFENDER!$C:$C,SUMMARY!$E$3)

 

Highlighted

@gillisrj 

If I understood correctly your data is linked based on Case ID. With that you may use Case ID to return full name, like

=IFNA(TEXTJOIN(", ",,INDEX(OFFENDER!$B:$C,MATCH($A8,OFFENDER!$A:$A,0),{1,2})),"")

Please check in attached file.

Highlighted
It worked!