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.