Automatic numbers.

[KasperHH]

Office 365, PC

Hi.

I'm looking for a function/formula to automatic adjust numbers when I change the first one.

In cell A3 I have the number 4.1 and underneath 4.1.0 - 4.1.1 - 4.1.2......

If I change cell A3 to 4.2 I would like it for all the numbers below to change accordingly... so 4.2.0 - 4.2.1 - 4.2.2 and so on.

How can I achieve that??

Regards Kasper

[#2]

@KasperHH 

I would do it very simply with search and replace (CTRL + H).

Search: 4.1

Replace: 4.3

You have to be careful with 4.4.1 anyway if there is such an entry.

If you want to use a function, you could use the following in cell A4 and then copy it down. In cells A24 or A26 you would then have to delete manually.

=IF(B4="","",A$3&"."&SUM(--(A$3:A3<>"")))

[#3]

Thanks a lot for your reply @dscheikey

The function gives me a #NAME? error all the way down. Unfortunately I'm not excel-intelligent enough to figure out what's wrong with it.
Can you give it a second look please?

Please note that here in Denmark we use ; instead of , to separate arguments.

Regards Kasper

[#4]

@KasperHH 

Prøv venligst denne i den danske oversættelse:

=HVIS(B4="";"";A$3&"."&SUM(--(A$3:A3<>"")))

[#5]

@dscheikey
Of course.. I'm an idiot :) I forgot to translate the IF, to the danish word HVIS....

works like a charm...
Thanks again... :)

[#6]

@dscheikey 

Well except from the fact that the highlighted cell A5 should be 4.4.0 and not 4.4.1. :)

Any solutions to that u think?

Thanks

[#7 · Accepted solution]

@KasperHH 

For cell A4

=HVIS(B4="";"";A$3&"."&SUM(--(A$3:A3<>""))-1)