Excel – Mirror column on separate sheet in Excel 2010

microsoft-excel-2010

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:

=OFFSET(Sheet1!$A$1,Row(A1)-1,COLUMN(A1)-1,1,1)
  • 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:

  1. If there are blanks in between the data (in Sheet1), some formatting or additional formulas may be necessary, to avoid lots of zeros showing.
  2. If you try to use some formula as "counta" or "average", results may be altered by these zeros (you may hide them, but they will still affect formulas.
Related Question