You are here:

Excel/Auto numbering Macro

Advertisement


Question

Drawing2
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


Answer
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
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

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.