AboutScottGem Expertise I have been an IT professional for over 16 years. During that time I have accumulated a large store of knowledge and experience. This allows me to assist with a large variety of technology issues. My specialty is Database design primarily with MS Access. However, I can answer questions on many different IT related topics.
Experience I have worked in IT support for over 16 years. I have also helped people in cyberspace for a good portion of that time. I have been a volunteer staffer on Prodigy and AOL. I was the #2 ranked expert at AskMe.com (#1 in Technology) and am currently ranked at or near the top at Answerway.com and PointAsk.com.
Expert: ScottGem Date: 8/5/2007 Subject: Automatic renumbering in Excel
Question QUESTION: Our small newspaper needs to create a database of subscribers from which we can print labels in what the post office calls Walk Sort order, the order in which the mail carrier hand delivers the mail on his route. The number associated with a particular subscriber is not fixed because if another subscriber is added on that mail carriers route, it may fall between two existing entries. So the Walk Sort column needs to automatically renumber when an entry is added or deleted. In Excel I can initially set up the Walk Sort column to automatically assign the next sequential number by using a formula of =F1+1, =F1+2, etc. What I can't get it to do is automatically renumber up or down if I add or delete an entry in the middle of the database. Is this possible in Excel?
ANSWER: If you are going to do a database you should do it in Access. But you can do what you want with a formula
=F1+1 adn then copy that formula down the column. When you copy the formula it uses relative addressing. So when you copy the formula from F2 to F3, the address will change. Whenever you insert a row, just copy the formula back down the column.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
---------- FOLLOW-UP ----------
QUESTION: Maybe I'm doing it wrong, but when I do that it doesn't renumber the following rows sequentially. So I end up with two rows with the same number. If I then print the subscriber labels sorted by that column, I'll have two with the same number.
Answer Assuming your first data row starts in Row 2, the formula in F2 should be =F1+1. When you copy that formula to cell F3 it should change to =F2+1. The result in cell F2 should be 1 and F3 should be 2. If you get a Value error, then you have to start the first data row with a 1.
I just tested this and it works.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
