excelexcel-formula

How can I change the following formula from concat to Indirect so I don't get any REF error

The formula that I concat is: How can have the same info using Indirect?

 =CONCAT('2101'!C2," - ",'2101'!C3," - ",'2101'!C4," - Admit: ",'2101'!C5)

This is what I use when is not CONCAT, but I have not been able to combine multiple cells with Indirect.

=IF(ISBLANK(INDIRECT("'"&B2&"'!"&"C8")),"",INDIRECT("'"&B2&"'!"&"C8"))
Solution

  • You can use:

    =CONCATENATE(INDIRECT("'2101'!C2"), " - ", INDIRECT("'2101'!C3"), " - ", INDIRECT("'2101'!C4"), " - Admit: ", INDIRECT("'2101'!C5"))

    or:

    =TEXTJOIN(" - ", TRUE, INDIRECT("'2101'!C2"), INDIRECT("'2101'!C3"), INDIRECT("'2101'!C4"), "Admit: " & INDIRECT("'2101'!C5"))