How to mirror another sheet and automatically update

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hi there,
Is there a clean way to mirror to sheets such that when a row is inserted or deleted in the main sheet, the mirrored sheet automatically updates? Right now I have a work-around, which requires two manual steps.

Here's what I have so far. In my input tab I put the name, salary, and portion of time worked per program for each employee.
Mr.Excel question.xlsx
ABCDEFGHIJ
1SalaryTotal FTEProgram 1Program 2Program 3Program 4Program 5Actual spreadsheet has 50 columns of data
21Name 11000.70.40.3
32Name 22000.850.10.150.10.5
43Name 330010.150.20.50.15
54Name 44000.750.60.130.02
65New employee5650.230.23
76Name 55000.50.5
87Name 660011
98Name 77000.70.20.5
109Name 88005131
1110Name 990033
1211Name 1010000.80.40.4
1312Name 1111000.20.2
1413Name 1212000.20.2
1514Name 13130011
1615Name 1414003.730.7
1716Name 1515000.50.5
1817Name 1616000.40.4
1918Name 17170011
20...
Input
Cell Formulas
RangeFormula
D2:D19D2=SUM(E2:Z2)

Then, in my calculation tab, I calculate dollar amount. I've done the formulas so if I add or delete rows in the Input sheet, I can renumber Column A and the change flows through to the Calculation sheet.
Cell Formulas
RangeFormula
C1:G1C1=Input!E1
B2:B18B2=VLOOKUP(A2,Input!$A$2:$B$20,2,FALSE)
C2:G18C2=VLOOKUP($A2,Input!$A$2:$X$20,3,FALSE)*(VLOOKUP($A2,Input!$A$2:$X$20,MATCH(C$1,Input!$A$1:$X$1,0),FALSE)/VLOOKUP($A2,Input!$A$2:$X$20,4,FALSE))

This is a potentially messy work-around, so I'm wondering if there is a cleaner solution. I don't really want to use an INDIRECT because that will slow the spreadsheet down a lot, but if that's the only way to do it let me know, as I may need some help with that formula too, I haven't used it often.
thank you so much!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows
On the second sheet, instead of doing a lookup to get the name (shown currently in column B) you can eliminate column A from both sheets and use this formula:

Excel Formula:
=INDEX(Input!$A:$A,ROW())

and the Name column in the second sheet will always be in sync with the Input sheet regardless of insertions and deletions.

You can use the same logic in the other columns instead of using VLOOKUP on column A. For example, keeping the assumption that we get rid of column A, the formula in column C (which will become column B) becomes

Excel Formula:
=INDEX(Input!$B:$B,ROW())*OFFSET(INDEX(Input!$B:$B,ROW()),0,MATCH(B$1,Input!$A$1:$X$1,0)-1)/INDEX(Input!$C:$C,ROW())

I did not try to rebuild your file to test this.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,610
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top