Let's say I Have that formula in the picture at B2. I always thought it is natural to add more rows to the top of the page to add more data so I can see them first as more recent. My problem is now that when I insert a new row at line 4 for example the formula changes to = MOD (count (B5:B), 12), so It misses the information I have just entered. How to tie this formula to the 4th row no matter what happens to the table?
I came up with Idea of just adding an empty row and adding information after that or use INDIRECT somehow, but from what I understood it will hard to change the formula itself then dragging it to other columns. Say dragging it from B to C won't change it automatically to MOD (count (C4:C)) which is desirable here.
Well I think I am just looking for the best method.
Best Answer
Change your formula to include the blank row3.
Like this:
=MOD(COUNTA(B3:B),12)Inserting a new row
To insert a new row at the top of the range, click on the first row that contains data - in this case, it is Row#4 ("12-Mar"). A new row#4 will be created.
Enter the date/data into that cell, and the adjacent cell in Column B.
Because the range in your formula begins with Row 3, any data in the new row will be automatically included in the formula.