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
HOURS
ASM_COUNT
TOTAL
OTHER
A_Sanchez
A_Holmes
B_Perez
C_Reynolds
M_Lopez
M_Reynolds
M_Tran
N_Fonseca
S_Hardegen
S_Hernandez
T_Stohler
Name01

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9"]Week_1[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]44[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]C_Hamish[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
Week_1	Week_2	Week_3	Week_4
Days Worked
Avg Hours Per Day
Avg Asm Per Dayt
TOTAL
='Input Table'!B11
='Input Table'!B12
='Input Table'!B13
='Input Table'!B14
='Input Table'!B15
='Input Table'!B17
='Input Table'!B18
='Input Table'!B19
='Input Table'!B20
='Input Table'!B22
='Input Table'!B23
='Input Table'!B24

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=595959]#595959[/URL] , align: center"]Hourly Average By Week[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=595959]#595959[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=595959]#595959[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=595959]#595959[/URL] , align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]6.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]7.67[/TD]
[TD="align: right"]8.25[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10.00[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]8.83[/TD]
[TD="align: right"]9.17[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5.93[/TD]
[TD="align: right"]7.05[/TD]
[TD="align: right"]7.11[/TD]
[TD="align: right"]6.22[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0.13[/TD]

[TD="align: right"]0.13[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0.38[/TD]
[TD="align: right"]0.63[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0.47[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0.50[/TD]
[TD="align: right"]1.06[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0.69[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.56[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0.47[/TD]
[TD="align: right"]0.70[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]0.79[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0.53[/TD]
[TD="align: right"]0.60[/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]0.63[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0.80[/TD]
[TD="align: right"]0.68[/TD]
[TD="align: right"]0.78[/TD]
[TD="align: right"]0.60[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0.78[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]0.44[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]0.85[/TD]
[TD="align: right"]0.70[/TD]
[TD="align: right"]0.90[/TD]
[TD="align: right"]0.98[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0.25[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.30[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0.88[/TD]
[TD="align: right"]1.33[/TD]
[TD="align: right"]0.90[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]19[/TD]

</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