Select your data table, from C16:K?? where Row ?? is the first blank row below the data. You need to have the blank row selected.
Enter a name for this range in the Name Box. I used myData for the formula below.
In Column C, in the cell where you want the formula, paste this:
Drag this across to Column K and then delete the formula in H and J.
When you insert new rows into the table, they have to go below Row 16, but above the first Blank Row. Selecting the Blank Row and using Insert Copied Cells should work fine.
Here's what's happening:
- The Named Ranged (myData) includes the Blank Row.
- If you insert rows inside the Named Range, they become part of the range.
- COUNTA(myData) counts all of the cells that contain data in myData.
- COUNTA(myData)/9 tells us how many rows are in myData since there are 9 columns. (The total number of pieces of data in a completely populated range divided by the number of columns gives us the number of rows. That is why I needed to know if every cell in the range contained data. If there are any blank cells in the range - other than the bottom row - the count will be wrong)
- COUNTA(myData)/9)-1 give us the number of rows to use in the OFFSET function.
- OFFSET(C16,(COUNTA(myData)/9)-1,0) gives us the last cell that contains data in myData.
- OFFSET(C16,(COUNTA(myData)/9)-1,0)*.9 multiplies that cell by .9