You are here:
- Home
- Computing/Technology
- Business Software
- Excel
- Auto numbering Macro
Excel/Auto numbering Macro
Advertisement
Question
I have the sheet: WBS&Action&Planning (see appendix) and using Excel2007
)
For numbering the actions in Column A I use the formula in cell A20 =IF(D20="","",MAX($A$19:A19)+1), cell A21=IF(D21="","",MAX($A$19:A20)+1), etc. All the way down. Every time I modify this sheet (add, delete rows) the numbering (formula’s) in Column A is not correctly anymore and I need to perform a copy&paste to setup the numbering correctly again.
I am looking for a macro which I can run when I have add, delete rows to have the action ID’s setup correctly again.
Can you help me?
Kind regards
Ellerd Beijert
Hi Ellerd
How are you?
This formula is fine. But, if you inset a row in, e.g. A23, this new cell doest not get the formula. You have to copy the formula from A22 to the new A23.
The only solution is to write a macro to insert new rows that not only insert the row but aso copy the formula to the new row.
Anyway, even without this macro, you do not need to copy and paste the whole column A. Just copy the formula from the A cell above.
Cheers
Adelaide
Related Articles
- Fill Handle - Using the Fill Handle to Copy Formulas in Excel
- Excel VLOOKUP Array Formula - Copying the VLOOKUP Array Formula
- Fill Handle - Auto Fill Handle - Copy Data and Formulas with the Fill Handle
- Copy and Paste - Excel 2007 Copy and Paste
- Subtract Dates Excel - Use the YEAR Function to Subtract Dates in Excel
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Excel
Answers by Expert:
- Mary A. Lindsey
- Nathan Head
- Raj Narayan Banerjee
- Chad Welch
- Ken Cowen
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bill Hermanson
- Jerry Beaucaire
- Michelle Howell
- Isaac
- Edward H. Mori
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- JP
- Richard Rost
- Victor Lan
- Tushar Sakhalkar
- Philip Buckley
- David W. Cofer
- Amal
- Bob Umlas
- Jessica L.
- Shahid
- Jan Karel Pieterse
- Craig
- Adelaide carvalho
- Sixthsense
- Bill
- Anne Troy
Adelaide carvalho
Expertise
I will be able to answer any question on Excel and Visual Basic for Applications - class modules, recursive procedures and functions are my favourite
Experience
Past/Present Clients
Several readers of my Books think they are useful.
©2012 About.com, a part of The New York Times Company. All rights reserved.