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.

8
  • 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, 2016 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, 2016 at 14:50
  • @Paul See my EDIT#1 Jun 4, 2016 at 15:14
  • @Wishwas See my EDIT#1 Jun 4, 2016 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) Jun 5, 2016 at 4:36

1 Answer 1

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

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .