Concentrate formula referred to cell

[seercoven]

Hi Guys,

Currently i'm using below codes and manual edit when the rows changes. Is there any ways we can simplify this (other than concat) or reference to specific cell and we just keyed in the row number.

For eg, enter 5 if we need the row 5 data and the result will be something like "TESTA5-C5" instead?

=CONCATENATE("TEST ",A1," - ",C1)

[FDibbins]

Maybe something like this?
="Test "&INDIRECT("A"&Z19)&C1
where Z19 would contain your row number

[seercoven]

Hi FDibbins ,

As per my initial codes, both column A & C uses row 5 for the data.

If we were to use your data from row 19, is there any ways i can just key in 19 and i get result. It tough editing the formula every time the row# change.

[FDibbins]

If both A and C need to refer to the same row, adjust my formula to include INDIRECT for C as well...

="Test "&INDIRECT("A"&Z19)&INDIRECT("C"&Z19)

[seercoven]

Thanks for the codes. It works perfectly.

[FDibbins]

Happy to help