Problem with Serial Number in Excel 1

[FarzanaSaleem]

In an Excel Sheet, first column is Serial Number. This excel file is shared and used by all team members to enter goals for the coming month.

I added one for column A

Cell A1 = Serial Number
A2 = 1
A3 = A2 + 1

I copied A3 and pasted till A500

Now each member uses this sheet and enters his goals.

Problem arises when some member enters a goal and then, for some reason, removes the line.

For example Person A entered his goal on Serial # 5 (A6), and then deleted the entire row.

Now cells from A6 and onwards show an error #REF!

 

[GlennUK]

I have a question:

when the person deletes an entire row for example Serial #5 ( row A6 ), does the new row 6 ( which was row 7 ) have a serial number of 5 or 6?


Glenn.

 

[FarzanaSaleem]

I need its serial number to be 5

 

[xlbo]

instead of your current formula, try
=ROW()

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GlennUK]

You could use a formula like from row 3 downwards :

=INDIRECT("A"&ROW()-1)+1

but this won't cope with people inserting rows. If you need to cope with that situation you will need to have a VBA macro for it.


Cheers, Glenn.

 

[AnotherHiggins]

This should take care of it w/o any VB:
[ul][li]click on cell A3[/li]
[li]Go to Insert>Name>Define[/li]
[li]Type "Range" (or any other name you choose) into the box at the top.[/li]
[li]in the Refers To box at the bottom, type in =Sheet1!$B$1:$B2 (Note that there is no "$" in front of the 2, this allows for the range to "slide" as you move down the sheet)[/li]
[li]in cell A2, use this formula: =max(Range)+1[/li]
[li]fill that formula down the sheet as far as you like.[/li][ul]

The range that is refered to will always be from A1 through the row above the active cell. Since A1 isn't a number, the max of the range A1:A1 (refered to in cell A2) is 0. 0+1=1. Using this formula, it doesn't matter if someone deletes or adds a row. As long as A1 isn't deleted, it will always work!

Enjoy,
John

 

[xlbo]

or you could just use =ROW()
as I suggested - that doesn't care if rows getdeleted either

but clever idea though - kudos for that

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[AnotherHiggins]

Dang, Geoff. I completely overlooked your post. Yours is a simpler and more elegant solution. I figured out the trick I described for a problem I ran into a few months ago, but I was doing a lot more with the range than adding 1.

 

[xlbo]

As I said - clever idea - well worth posting in any case. Come to think of it - I should give you a star so it's marked out for others

Have a good weekend

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[AnotherHiggins]

Wow, that's my first star! Thanks!!!

 

[FarzanaSaleem]

Thanks

I used =ROW()-1 in cell A2 and pasted this formula up to A500. Now when I deleted Serial No. 5 (Row 6), then all serial numbers adjusted accordingly.

I need one more feature in this sheet.

When a person inserts a new row between two serial numbers, then serial numbers below it adjust accordingly but the inserted row shows a blank. For example, if I insert a new row between Serial No. 5 and 6 (A6 and A7), then
A6 = 5
A7 = BLANK
A8 = 7

I need A7 to show 6 automatically.

 

[FarzanaSaleem]

Anyone to help me please.

Thanks

 

[xlbo]

Needs code for this - ask in the VBA forum

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244