Mirror column on separate sheet in 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?

Solution 1:

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.

Solution 2:

this cannot be done with a simple formula. If you add or remove rows in Sheet1, a formula will not be sufficient to add or remove rows in Sheet2. You need a different approach for that.

One option is to create VBA that manages the deletion and addition of new rows.

Another way might be to hide rows instead of deleting them. Add a column where an employee is marked as active or inactive. Then filter the table to hide inactive employees.

A formula can be used to pull the names from Sheet1 into Sheet2, and also pull the acitve/inactive values for each row. For example, if the name is in column A in sheet1, then use this in Sheet2

=index(Sheet1!$A:$A,row())

If the status (active/inactive) is in column B, use =index(Sheet1!$A:$A,row()) in another column and then filter on that column.

There is one big inherent danger though: If the order of employees is changed in Sheet1, then the data in Sheet2 will all be wrong. The order of employees will still match the order in Sheet1, but the rest of the data will not travel with the new sort order.

An aproach with VBA would be more robust.