Have secondary sheets mirror master sheet

[MarkitaVessier]

Good Morning,

Currently I have one master sheet of employees. Every time I update this sheet I want the rest of the sheets to update with as well. This means I am constantly adding and removing columns.

I am currently using this formula:

=IF(OFFSET('Staff List'!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET('Staff List'!$A$1,ROW(A1)-1,COLUMN(A1)-1))

However it only mirrors the values. I need to have the formatting mirrored as well.

I realize that I need to use VBA as formulas are not complex enough.Example.xlsx

[mikerickson]

I don't understand what you want.
Your attachment doesn't show which cells you want to mirror. Lots of the formulas on that sheet reference a different workbook, Costed Schedule V2.xlsx


In general, a formula like

=VLOOKUP("cat", Sheet1!$A:B, COLUMN(Sheet1!B:B), False)

will drag and adjust to column insertion and deletion properly.

[MarkitaVessier]

I want Column A and Column B in the "Schedule" Sheet to mirror Column A and Column B from the "Staff List" sheet.

If I insert a row (or employee) under the "serve" section on the "Staff List" sheet, I want that new row to show up in the "Schedule" sheet. Because they are grouped in sections I want the formatting to follow.

[mikerickson]

In Schedule!A1, put the formula =INDEX('Staff List'!A:A,ROW(A1)) and drag the formula down and right.

If you want something absolutely fully automatic, you could put this code in the sheet module for 'Staff List' sheet

Please Login or Register  to view this content.

One conceptual problem you have is that if you have data in Schedule! column C, e.g. "a", "b", "c" are in A3:C3, and you insert a row into Staff List (e.g. row2) , that column C entry will now be in a different row (e.g. "a" and "b" will be in row 4 while "c" is still in row 3)

[MarkitaVessier]

Thank you for this, HUGE help!

Is there any way around this conceptual problem?

[mikerickson]

If Schedule is for viewing only, with no data entered on that sheet. You could put it far to the right on Staff List and then either split screen or mirror the far right section on a different sheet.

[MarkitaVessier]

Thank you