Ask Your Question

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

asked 2015-10-29 06:28:21 +0200

argh.pirate gravatar image

updated 2020-08-09 20:00:34 +0200

Alex Kemp gravatar image

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

image description

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-05-26 22:14:46 +0200

m.a.riosv gravatar image

Please take a look to TEXTJOIN() Help

edit flag offensive delete link more

answered 2020-05-26 21:46:39 +0200

LeroyG gravatar image

updated 2020-05-27 13:58:48 +0200


Thanks to @m.a.riosv 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.


edit flag offensive delete link more


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.

m.a.riosv gravatar imagem.a.riosv ( 2020-05-27 09:28:33 +0200 )edit


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 ';').

LeroyG gravatar imageLeroyG ( 2020-05-27 14:01:29 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2020-05-27 15:04:16 +0200 )edit

With "+1"?

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

LeroyG gravatar imageLeroyG ( 2020-05-27 18:01:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-10-29 06:28:21 +0200

Seen: 58 times

Last updated: May 27