Dynamic line numbering when rows get added or swapped

[BlackieHamel]

I have a simple table with line numbers (1,2,3. . .x) appearing in Column A. Cell A1 is 1, Cell A2's formula is A1+1, Cell A3's formula is A2+1, and so on. This creates problems if anything gets moved.

What is a good way to keep the line numbers sequential when a row gets added, deleted, or moved?

Thanks -- Blackie

 

[Joe4]

If you just want row numbers starting at 1, then place this formula in cell A1 and copy down:

=ROW()

 

[BlackieHamel]

If you just want row numbers starting at 1, then place this formula in cell A1 and copy down:

=ROW()

Perfect, Joe4! Thanks so much.

 

[Joe4]

You are welcome!

The key is to not put any argument between the (). The defaults to the current row number that the formula is found in.

 

[BlackieHamel]

You are welcome!

The key is to not put any argument between the (). The defaults to the current row number that the formula is found in.

Okay, here's a more ambitious task. My table has shading in the even-numbered rows. I tried to do that by selecting the whole table and setting Conditional Formatting with the formula to apply shading if

=iseven(row())

But when I add a row, that shades the entire table below the insertion. Can you see what I'm doing wrong?

 

[Joe4]

But when I add a row, that shades the entire table below the insertion. Can you see what I'm doing wrong?

No, that shouldn't be possible, based on that one rule.
Are you sure that you don't have any other Conditional Formatting rules lingering out there?

 

[BlackieHamel]

No, I am not experienced with Conditional Formatting, so I'm quite sure there are no other rules there. But as I think about it, it seems like I need an IF statement: if the row number is even, apply shading; if the row number is even, use a white (non-shaded) background.

And you gave me an idea when you suggested that there can be multiple Conditional Formatting rules affecting the same range. I tried to select the whole table and apply two Conditional formatting rules, but still, when I add a row, it seems to just keep the shading of the row above.

Blackie

 

[Joe4]

it seems like I need an IF statement: if the row number is even, apply shading; if the row number is even, use a white (non-shaded) background.

No, you don't need an IF, and you do not need multiple rules - just one, if you want to shade every other row (and leave the others "as-is").
You would only need two rules if you wanted to colors (and neither one the standard white background).

Try this:
1. Remove ALL Conditional Formatting from your Sheet
2. Select all the rows you might ever use
3. Entering the following CF rule:

=ISEVEN(ROW())

4. Choose the formatting shading color that you want
5. Click OK

*Also make sure that the Calculation mode on your worksheet is set to Automatic.

If you insert rows WITHIN this range which has been Conditionally Formatted, the new rows should inherit this rule, and everything should continue to work.

 

[BlackieHamel]

That did the trick! Thanks so much, Joe4.

 

[Joe4]

You are welcome.