ive got a huge excel workbook that needs to change. in the first column there is what amounts to a numbered list. and, using excels number formatting options, it used to behave nicely. at least, the people that had it before me said that it worked properly. all of the numbers have since been changed.
the previous format was like this

1
1.1
1.2
1.2.1
1.2.2
1.2.3
1.3

they wanted to change all of the numbers to two digits, so now it looks like this

01
01.01
01.02
01.02.01
01.02.02
01.02.03
01.03

what is supposed to happen is this
if a new row is added, it should fill in that cell with the appropriately labeled number that is next in the sequence directly above it. if the number is changed it should change the whole numbering accordingly
initial

01
01.01
01.02
01.02.01
01.02.02

then we insert a row in between '01.02.01' and '01.02.02', this new row should have the first cell as '01.02.02' and change the one below it to '01.02.03'

01
01.01
01.02
01.02.01
01.02.02 <--new row
01.02.03

now, if we were to change the new cell from '01.02.02' to '01.03' the very next cell should change to '01.03.01'

01
01.01
01.02
01.02.01
01.03
01.03.01

it seems to me like there should already be a feature that exists that does this. i cant seem to figure it out, but then, this is my first time ever trying to play with excel. if i could paste these spreadsheets into word and have it number them properly, i would do so. but word doesnt recognize them as an already existing numbered list. which is a huge pita.

any suggestions at all?

I do NOT believe it does this. To the best of my knowledge, you have to write a function that rewrites that entire column every time the page loads Hmmm... I wonder if it could be done based on the line number. That way you could write a single formula that does a comparison to the line it's on and does a recalculation every time a row is added. (you would of course have to copy the formula to the new row.) By the way, =row(A1) will return the row number for row 1, so your row numbers don't have to start at row 1, If you put row(A1) into row 10, it would still return a 1. Every subsequent change you made after would have the formulae readjusted for every insertion.

Wait, nevermind. I missed that last part about inserting a number that was NOT in keeping with the entire sequence. Hrmmmm...... This is going to bug me.

Can you post the original format that was being used? Was there any function setup that controlled the autonumbering? An in worksheet function wouldn't do this automatically. I'm not sure about a format but I don't think so. If you can give me some more details about what the old spreadsheet did or post a portion of it, I'll look at it further.

Yep. post the original formulas. That would be a big help.

<edit>
Wouldn't something like this have to be implemented in a macro of some kind?
Empty cells would be filled down with incremental values. Cells with values would be used to interrupt and reseed the incrementation. Finally the fill would be limited to a defined range.

im not entirely sure what youre asking. the original format was the same numbering system, only without the '0' added.
and there were no formulas present. from what ive been told, my understanding is that some feature of excel worked when it was sans '0' and broke when the '0's were added. as much as i would love to post the original spreadsheet, i cant. it would be a huge process to censor everything on it. theres about 4000 rows i would have to change.

Under Cell formatting in the menu bar, you can setup a custom format for a cell. I thought that was what you were saying was being used.

If you can save a copy and delete every column except the numbering column, then post that, that would be enough for now. If you want you can email it to me, abarber(at)excite(dot)com.

Grasping at straws now.

What it cannot be:
-Formulas are out. You couldn't enter a value without wiping the formula.
-Cell formatting doesn't really change the value input, it just makes it look different. Nor can the format of one cell impact the values of another.
-Conditional formatting can't change the value in a cell.

Was there a limit on the number of levels? 1.2.3.4.5.6...
Do you still have a version that works?
Dose it work if the zero's are taken out?

The more I think about it this sounds like an add-in macro of some kind. One that is automatically validating and rebuilding the column everytime the sheet is calculated. That kind of item could be very susceptable to breakage if a user gave it garbage input. Referring to the zeros of course. If that is the case taking the zeros out should restore the function.


Are there add-ins are installed in the workbook? What are they?

Btw: This is something I could use. For a WBS listing.

there are no macros present at all. the value doesnt matter, as long as it looks like they expect it to. its not calculating anything. it is being used as a numbered list. i sent mach a copy. its very basic, as spreadsheets go. just huge
should be no limit on depth. but the max the first section will go at this point in time is '10'
so.. there wont be any '11.01'

Ok this is bugging me now. I'm thinking gbeer is right. Under add-ins, what is checked? Is there another workbook that this is launched from?

gbeer, fyi there's nothing in cell (format is text) or functions (no macros) included that would do this as far as I can tell.

mach, i dont have it with me. but that copy i sent you is exactly the same as the one i have, except that all the stuff you dont need to read is censored

The only way I can think of doing this is with a macro. One cell calls a macro with the range for the whole column. Then, when the column changes (insert, edit, etc.), the macro then runs and renumbers the column.

Yah got it but Excel can have code that is not within the workbook but called as an add-in which are referenced under Tools>Add-ins.

Coupe of questions...how are the users inserting rows? Insert>Rows menu item? I noticed that the example text typed next to the numbers are indented a number of columns over depending on the numbering. For instance, 01 is in first column, 01.01 is in the second column, 01.01.01 is in the third and so on. Do the users enter the text this way or is it done automatically based on the numbering?

afaik everything was done by hand up to this point. they said that it was 'working for us until we changed the numbering'. i think its safe to assume that any solution to this problem would come from scratch.

Ok, The best I can say at this point is put the numbering back into the old format. Hopefully it will start working again. Then insert a new column next to it and put in a formula that will generate what you want.

column a= 1.1.1
column b= formula that generates leading zeros.

Then take column a out of the print range.