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

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

argh.pirate 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