Auto Adjust Numbering After Delete/Insert New Row

[kumara_faith]

Hi,

I have a spreadsheet which is project tracking sheet as below:

Excel Workbook
	B	C
8	1.00	Medical Business
9	1.10	Finalize Document Matrix for scanning
10	1.11	List of documents that will require scanning
11	1.12	Volume of documents that will require scanning
12	1.13	Indexing criteria for each documents
13	1.20	Confirm Business Rules
14	1.21	Establish turn around time
15	1.22	Establish work process for the scanning process
16	1.30	Resource Planning
17	1.31	Collate updated scanning volume
18	1.32	Analyze existing and future scanning volume
19	1.33	Make necessary change in existing work process to accommodate new volume
20	1.34	Approval and installation of software
21	1.35	Submit request for additional staff based resource and volume if necessary
Project Tracking

On a very frequent basis, i will need to add new task by inserting new rows in between existing rows. For example, I would need to add a new task between row number 1.31 and 1.32. When I do this, I need to fill the number manually in the new row and adjust all other numbering manually.

Is there a way to get excel to automatically fill in the number for the new row and adjust all other numbering automatically ? Excel should also adjust all numbering automatically when any existing rows are deleted.

is this possible ? Appreciate assistance.

I have a partial croos post here:

http://www.excelforum.com/excel-programming/755883-auto-adjust-all-formatting.html

 

[crook_101]

Hi,

You won't get your own numbering format as there is no regular pattern, you could use something like the below but you will still have to drag the formula down when you insert/delete rows to refresh it...... HTH

Ian.

Excel Workbook
	A	B
1	1	Medical Business
2	1.1	Finalize Document Matrix for scanning
3	1.2	List of documents that will require scanning
4	1.3	Volume of documents that will require scanning
5	1.4	Indexing criteria for each documents
6	1.5	Confirm Business Rules
7	1.6	Establish turn around time
8	1.7	Establish work process for the scanning process
9	1.8	Resource Planning
10	1.9	Analyze existing and future scanning volume
11	2	Make necessary change in existing work process to accommodate new volume
12	2.1	Approval and installation of software
13	2.2	Submit request for additional staff based resource and volume if necessary
Sheet2

 

[kumara_faith]

Hi,

Thanks Cook_101.

I was trying this from the following page:
http://blogs.techrepublic.com.com/msoffice/?p=293
1.In A1, enter this formula:
=TEXT(Row(A1),"000-000")
2.Enter the name of the first product in B1.
3.Enter the price of the first product in C1.
4.Copy cell A1 to A2.
5.Enter the name of the second product in B2.
6.Enter the price of the second product in C2.
7.Select A1:C2.
8.Go to Data | List and select Create List. (In Excel 2007, press [Ctrl]T, then click OK.)
Now when you type the name of the next product in B3 and press [Tab], Excel will automatically fill in the next number for you.

Excel Workbook
	A	B	C
1	Number	Product	Price
2	000-002	Binder	$ * * * * * * 2.50
3	000-003	Pencil	$ * * * * * * 0.80
4	000-004	Ruler	$ * * * * * * 0.30
5	000-005	File	$ * * * * * * 2.60
Sheet3

This works great because it auto inserts a number whenever a new row inserted and adjust accordingly when a row is deleted.

However, after performing all the steps above, cell A2 has a numbering of 000-002 instead of 000-001.

I did the following:
1) Changed the formula in cell A2 from =TEXT(ROW(A2),"000-000") to =TEXT(ROW(A1),"000-000")

However, when I did this, it does not auto fill the number on a new row correctly.

Example: When I insert a new row in between number 000-003 and 000-004, I am getting the following:

Excel Workbook
	A	B	C
1	Number	Product	Price
2	000-001	Binder	$ * * * * * * 2.50
3	000-002	Pencil	$ * * * * * * 0.80
4	000-003	Ruler	$ * * * * * * 0.30
5	000-004	*	*
6	000-004	File	$ * * * * * * 2.60
Sheet3

Is there a way I could resolve this ?

 

[crook_101]

Hi,

I'm using 2003 and not sure of any changes between the two - looks as though it is not refreshing/recognising the new inserted/deleted row.

When creating a list you should have started with your header in A1 and in A2 with the formula

=TEXT(ROW(A1),"000-000")

and when you Data>>List>>Create , ensure that the headers box is ticked (2003)

To insert rows you need to refresh the list you will have to copy the above formula down again after you have deleted/inserted rows I'm afraid as you will just get what is shown below, as you are now aware....

Excel Workbook
	A	B	C
1	Number	Product	Price
2	000-001	Binder	$2.50
3	000-002
4	000-002	Pencil	$0.80
5	000-004
6	000-004
7	#REF!
8	#REF!	ruler	$0.3
9
Sheet1

 

[kumara_faith]

Thank you for your reply crook_101.