Originally Posted by
e_lad
The only other thing is....can you possibly make the formula go one step further to make provision for the table to move. Eg. when I insert rows at the top of the table i.e. above Row 3, it doesn't generate the #VALUE error message.
Revise formula to:
Originally Posted by
e_lad
NBVC, that's great.
I really want to understand how the formula works so that I can learn to customise next time round. Which part of the formula tells it to stop the calculation at the row just above the cell of the formula?
The range to look in is determined by:
INDEX(C:C,MATCH("Day",A:A,0)+1):INDEX(C:C,ROW()-1)
This indexes a range between the first row after "Day" is found in column A to the row before the current row (the row the formula is in).
Syntax of Index: INDEX(Array, Row_Num,[Col_Num]) where Col_num is optional and required only if Array is 2 dimensional. When nested in a formula it returns the cell reference at the intersect of Row_Num,Col_Num within Array... when used on its own, returns a value at the cell intersecting at Row_Num and Col_Num.
So:
INDEX(C:C,MATCH("Day",A:A,0)+1)
says find the position (or row number) matching the word "Day" in column A, and add 1 to it... then index that in column C.... so as it is now, it would return C4.
INDEX(C:C,ROW()-1)
says index column C with the row number previous to the current row. So it would return C12.
Bookmarks