How to mirror another sheet and automatically update

[FlowersinExcel]

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
	A	B	C	D	E	F	G	H	I	J
1			Salary	Total FTE	Program 1	Program 2	Program 3	Program 4	Program 5	Actual spreadsheet has 50 columns of data
2	1	Name 1	100	0.7		0.4			0.3
3	2	Name 2	200	0.85	0.1	0.15	0.1	0.5
4	3	Name 3	300	1	0.15	0.2	0.5	0.15
5	4	Name 4	400	0.75	0.6	0.13		0.02
6	5	New employee	565	0.23					0.23
7	6	Name 5	500	0.5					0.5
8	7	Name 6	600	1	1
9	8	Name 7	700	0.7		0.2	0.5
10	9	Name 8	800	5	1	3		1
11	10	Name 9	900	3			3
12	11	Name 10	1000	0.8		0.4			0.4
13	12	Name 11	1100	0.2			0.2
14	13	Name 12	1200	0.2			0.2
15	14	Name 13	1300	1	1
16	15	Name 14	1400	3.7		3		0.7
17	16	Name 15	1500	0.5					0.5
18	17	Name 16	1600	0.4			0.4
19	18	Name 17	1700	1	1
20	...
Input

Cell Formulas
Range	Formula
D2:D19	D2	=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.

Mr.Excel question.xlsx
	A	B	C	D	E	F	G
1			Program 1	Program 2	Program 3	Program 4	Program 5
2	1	Name 1	0.00	57.14	0.00	0.00	42.86
3	2	Name 2	23.53	35.29	23.53	117.65	0.00
4	3	Name 3	45.00	60.00	150.00	45.00	0.00
5	4	Name 4	320.00	69.33	0.00	10.67	0.00
6	5	New employee	0.00	0.00	0.00	0.00	565.00
7	6	Name 5	0.00	0.00	0.00	0.00	500.00
8	7	Name 6	600.00	0.00	0.00	0.00	0.00
9	8	Name 7	0.00	200.00	500.00	0.00	0.00
10	9	Name 8	160.00	480.00	0.00	160.00	0.00
11	10	Name 9	0.00	0.00	900.00	0.00	0.00
12	11	Name 10	0.00	500.00	0.00	0.00	500.00
13	12	Name 11	0.00	0.00	1100.00	0.00	0.00
14	13	Name 12	0.00	0.00	1200.00	0.00	0.00
15	14	Name 13	1300.00	0.00	0.00	0.00	0.00
16	15	Name 14	0.00	1135.14	0.00	264.86	0.00
17	16	Name 15	0.00	0.00	0.00	0.00	1500.00
18	17	Name 16	0.00	0.00	1600.00	0.00	0.00
Calculation

Cell Formulas
Range	Formula
C1:G1	C1	=Input!E1
B2:B18	B2	=VLOOKUP(A2,Input!$A$2:$B$20,2,FALSE)
C2:G18	C2	=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!

 

[6StringJazzer]

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:

=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

=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.

 

[FlowersinExcel]

Fabulous - this worked great! Thank you so much!