Sorry really hard to explain this one

[Jtucker10278]

So I have my Input table, Days across the top, names down the side. but now I need to add a new employee to the table in order by first name so as you can see below

C_Hamish will need to go between C_Reynolds and M_Lopez. (I know R is after H but the company report prints alpha by first name which I have shortened)

To do this is no problem I just insert a row and add the name

	B	C	D	E	F	G	H
3		Week_1
4		Mon	Tue	Wed	Thu	Fri	Sat
5		1	2	3	4	5	6
6
7	HOURS	8	8	8	8	8
8	ASM_COUNT	11	11	10	8	10	0
9	TOTAL	44	58	43	37	55	0
10
11	OTHER	1	1
12	A_Sanchez	4	3	1	1	6
13	A_Holmes	11	5	1	2	1
14	B_Perez	2	11	7		2
15	C_Reynolds	3	1	4		7
16	C_Hamish
17	M_Lopez	1	3	6	8	3
18	M_Reynolds	6	11	4	5	6
19	M_Tran	3	7	3	6	12
20	N_Fonseca	12	9	7	1	5
21	S_Hardegen
22	S_Hernandez	0	6	6	11	12
23	T_Stohler	1	1	4	3	1
24	Name01

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Input Table​

the problem is that I have 4 other sheets with tables that have totalled the weeks in different ways and in each of these tables instead of putting the names down the side I referenced the names in the input table. (My thinking was that this way I wouldnt have to update each of my 4 other tables)

The problem is that when i insert a new row 16 in the input table excel auto updated the cells referencing back to the name to skip the newly inserted row my cell references jump from 15 to 17

	A	B	C	D	E
1		Hourly Average By Week
2		Week_1	Week_2	Week_3	Week_4
3	Days Worked	5.00	5.00	6.00	6.00
4	Avg Hours Per Day	8.00	8.00	7.67	8.25
5	Avg Asm Per Dayt	10.00	8.17	8.83	9.17
6	TOTAL	5.93	7.05	7.11	6.22
7
8	='Input Table'!B11	0.13		0.13
9	='Input Table'!B12	0.38	0.63	0.74	0.47
10	='Input Table'!B13	0.50	1.06	0.94	0.95
11	='Input Table'!B14	0.69	0.50	0.98	0.56
12	='Input Table'!B15	0.47	0.70	0.83	0.79
13	='Input Table'!B17	0.53	0.60	0.57	0.63
14	='Input Table'!B18	0.80	0.68	0.78	0.60
15	='Input Table'!B19	0.78	0.94	0.96	0.44
16	='Input Table'!B20	0.85	0.70	0.90	0.98
17	='Input Table'!B22	0.25	0.33	0.43	0.30
18	='Input Table'!B23	0.88	1.33	0.90	0.95
19	='Input Table'!B24

<tbody>
</tbody>

Sheet1​

Any thought would be appreciated

 

[stunnrock]

Instead of:

='Input Table'!B11

use:

=index(

'Input Table'!​

$B$11:$B$24,row()-row(A$8:A$8)+1)

and copy it down as far as required.

 

[wideboydixon]

Or you could cater for further additions like this:

=INDEX('Input Table'!$B:$B,ROWS($A$8:$A8)+10)

And copy it down as far as necessary to deal with as many new employees as you like.

WBD