SOLVED

Relative Cell Reference

Copper 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

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

@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)

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 

best response confirmed by gillisrj (Copper 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)

 

@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.

It worked!
1 best response

Accepted Solutions
best response confirmed by gillisrj (Copper 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)

 

View solution in original post