# Formula for Ascending Sequence

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.

• There doesn't appear to be a reason you are using the row number as your reference. Is there a reason? There are many ways to get a sequence of numbers, having a better understanding why you are doing this would help find the right one. – Paul Jun 4 '16 at 13:56
• After the sequence is generated, get rid of the formula. Simply select and copy the data generated thru formula, and replace it with 'paste special values' – Wishwas Jun 4 '16 at 14:50
• @Paul See my EDIT#1 – Gary's Student Jun 4 '16 at 15:14
• @Wishwas See my EDIT#1 – Gary's Student Jun 4 '16 at 15:15
• You can use `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)` – Scott Craner Jun 5 '16 at 4:36

## 1 Answer

Here's my answer with screenshots.

I changed both formulas to use rows in a range that would not be deleted. It is not entirely bulletproof, but should work well depending on the range you select.

And if you re-insert the missing row, the formulas will re-adjust, but the proper count will remain.

# Before deletion ## After deleting Row 3 