I am trying to mirror a column from one excel sheet to another within the same excel book. The goal is to have the second sheet update as the first sheet is edited. Basically, as I add/remove rows in the first sheet, it will do the same automatically in the second sheet.
My column has employee names, and as people move in/out of the office I will be adding/removing them accordingly. My sheets track different data, but the employees are all the same. My first sheet is the primary sheet and the rest pull data accordingly. Would a mirroring formula work? Also, how do you get it to apply to the entire column?
Best Answer
Here goes an idea whithout VBA: "offset".
Lets say your original data is in "Sheet1" and you want to mirror every cell at "Sheet2". For any cell on Sheet2 you can use:
Sheet1!$A$1
: is you main reference cell. This is the only cell you can´t move or delete. You can leave Column 1 blank and hide it, to prevent this from happening.Row(A1)-1
: "Row(A1)" will figure out in which row of Sheet2 you are in. "-1" is to neutralize the offset (you wan´t to read the very same row you are in, not the following one).COLUMN(A1)-1
: same as above, but for figuring out your column.,1,1)
: sets the size of the range to a cell (1 by by)As long as you don´t remove your main reference cell (A1, for this example) I believe it should work, no matter what you do on Sheet1.
A few issues you may find with this method: