Jul 04 2020 02:16 PM
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.
Jul 04 2020 02:47 PM
@gillisrj , it would be helpful to see a sample. Can you upload after stripping out any confidential information?
Jul 04 2020 03:33 PM
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)
Jul 04 2020 06:40 PM
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
Jul 04 2020 08:31 PM
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)
Jul 05 2020 07:16 AM
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.
Jul 04 2020 08:31 PM
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)