0

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:

enter image description here

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:

enter image description here

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:

enter image description here

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

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

enter image description here

After deleting Row 3

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.