Need Sheet2 to always mirror Sheet1 but w/ some columns combined

[argh.pirate]

I want Sheet2 to be an exact copy of Sheet1 except I want some columns from Sheet1 combined in the Sheet2 version. For example:

The problem is I need it to be able to work when I add and remove rows from Sheet1. So when I use the method =CONCATENATE(Sheet1.A1,Sheet1.B1,Sheet1.C1) and add a line on Sheet1 between Rows 1-2, the references on Sheet2 update and the new line is non-existent on Sheet2.

I also need empty cells to simply be empty. Not 0 or #REF! That way, when I have something like “Theodore Roosevelt” who has no middle name, I don’t want it to show as Theodore0Roosevelt or Theodore#REF!Roosevelt

I found this method that seemed to work but I had to place the coordinates in quotes for it to work and that prevented me from being able to use the AutoFill Series feature. I don’t know a thing about INDIRECT so that might be why.

=CONCATENATE((INDIRECT("Sheet1.A2")),(INDIRECT("Sheet1.B2")) ,(INDIRECT("Sheet1.C2")))

[LeroyG]

argh.pirate,

Thanks to @mariosv you will not need to add the extra space after each word in Sheet1 proposed in the Original answer.

=TEXTJOIN(" ";TRUE();INDIRECT("Hoja1.A"&CELL("row"));INDIRECT("Hoja1.B"&CELL("row"));INDIRECT("Hoja1.C"&CELL("row")))

Original answer:
A bit late but it work. Yet you will need to add an space after each word in Sheet1.

=CONCATENATE(INDIRECT(“Sheet1.A”&CELL(“ROW”));INDIRECT(“Sheet1.B”&CELL(“ROW”));INDIRECT(“Sheet1.C”&CELL(“ROW”)))

[mariosv]

I don’t know why is needed INDIRECT, =TEXTJOIN(" ";TRUE();Sheet1.A2;Sheet1.B2;Sheet1.C2) works for drag.

BTW, please use ‘;’ (semicolon) instead ‘,’ (comma) as separator, so it works no matter what language it’s used.

[LeroyG]

@mariosv,

INDIRECT for the need to delete rows in Sheet1.

Edited to change ‘,’ for ‘;’. And also changed in Tools - Options (restos de la última actualización, yo prefiero ‘;’).

[mariosv]

INDIRECT it’s a volatile function, it should be avoided as much as possible, in this case, can be use INDEX(SHEET1.A:A;ROW()+1) which is nonvolatile and quicker, or TEXTJOIN as array with array ranges.

[LeroyG]

With “+1”?

I understand the volatile issue: if I change the Sheet1 name… Thanks.

[mariosv]

Please take a look to TEXTJOIN() Help