When I add row to Excel, how to have references to that column extend to the new row?

Question

For example, I am just using COUNTA to see how many entries are in a given column. The exact formula is:

=COUNTA(E3:E25)

Now if I add an entry at row 26 (which is currently completely empty), I'd like this formula to then say:

=COUNTA(E3:E26)

without having to change it myself. Likewise I have a sum:

=SUM(C3:D25)

If I add information on row 26, I'd like this sum to recognize it somehow and update the D25 to D26. Any guidance or tips appreciated!

Answer 1

In Excel, there's a function called OFFSET you can use. For example, instead of:

=COUNTA(E3:E25)



You could do this (except all on one line, Yahoo likes to cut it off):

=COUNTA(OFFSET(

Sheet1!$E$3,0,0,

COUNTA(Sheet1!$E:$E),1))



That counts everything in a range going from E3 to the end of the cells in use in that column.

Answer 2

just insert a row between rows E3 & E25 and your formula will self adjust itself. If you add data into the new row, the formula wll pick it up. If you want to add it n the last row beforethe formula, the formula will not self adjust and not include he new data.

Answer 3

Sorry can only change it yourself. Excel updates the information automaticaly when you change it in a specified box, but to make it update info with the new box YOU have to change it.