I am trying to help someone generate a formula to return a simple sequential series of values. For example in cell C6 we inserted the formula:
=ROW(A1)
and in cell D6 we inserted the formula:
=ROWS($1:1)
When we copy these cells downward, we get:
The problem arises when he attempts to insert or delete a row well above the column of formulas. For example when row # 3 is deleted we get:
Both columns C and D are no longer valid. What's more, re-inserting the row does not restore the sequence either!
Is there an alternative formula that is not sensitive to deleting or inserting rows above the sequence ??
EDIT#1:
This is how the sequence is being used:
Column C contains the list of the items from column B that need to be sampled. The formula in E6 (when copied down) retrieves the samples.
All is good until a row above the header row is inserted or deleted.
ROW() - MATCH(TRUE,INDEX(E:E<>"",),0)
but this requires that all the cells above not have anything in them. Or you can use the title of the column in the Match instead;ROW() - MATCH("Samples", E:E,0)