Autofill Numbers In Merged Cells

[Bugalho]

I know there are some topics about autofill in merged cells, but I couldn't find what I need. I don't even know if it's possible.


Is there a way to autofill numbers in this kind of table? (see image bellow). There are cells merged in rows and columns. Numbers with 2 or 3 digits, depending on the number of columns.

Best regards
Élson

 

[jasonb75]

I didn't think it would be possible, the use of a wildcard match to count the number of merged columns is one of those things that I would have never thought of, but now that I've seen it I wonder why I didn't

I'm going to have a closer look at that formula later, see what else is making it tick.

 

[shaowu459]

Thnak you very much!! You are awesome!

You are welcome. I suggest do not merge cells if not necessary, if you have to merge cells, keep them in one column would be better.

 

[shaowu459]

I modified the formula and make it shorter

MrExcel.xlsx
	A	B	C	D	E
1	13		Chapter Name1
2	13.1			Section Name2
3	13.1.1				Title3
4	13.1.2				Title4
5	13.1.3				Title5
6
7	13.1.4				Title7
8	13.2			Section Name8
9	13.2.1				Title9
10	13.2.2				Title10
11	13.2.3				Title11
12	13.2.4				Title12
13	13.2.5				Title13
14	13.2.6				Title14
15	13.2.7				Title15
16	13.2.8				Title16
17	13.2.9				Title17
18
19
20	13.2.10				Title20
21	13.2.11				Title21
22
23
24
25
26	13.3			Section Name26
27	13.4			Section Name27
28	13.4.1				Title28
29	13.4.2				Title29
30	13.5			Section Name30
31	13.5.1				Title31
32	13.5.2				Title32
33	13.6			Section Name33
34	13.6.1				Title34
35	13.6.2				Title35
36	13.7			Section Name36
37	13.7.1				Title37
38	13.7.2				Title38
39	13.8			Section Name39
40	13.8.1				Title40
41	13.8.2				Title41
42	13.9			Section Name42
43	13.9.1				Title43
44	13.9.2				Title44
45	13.10			Section Name45
46	13.10.1				Title46
47	13.10.2				Title47
48	13.11			Section Name48
49	13.11.1				Title49
50	13.11.2				Title50
51	13.12			Section Name51
52	13.12.1				Title52
53	13.12.2				Title53
54	14		Chapter Name1
55	14.1			Section Name55
56	14.1.1				Title56
57	14.1.2				Title57
58	14.1.3				Title58
59
60	14.1.4				Title60
61	14.2			Section Name61
62	14.2.1				Title62
63	14.2.2				Title63
64	14.2.3				Title64
65	14.2.4				Title65
Sheet3 (3)

Cell Formulas
Range	Formula
A60:A65,A26:A58,A20:A21,A2:A5,A7:A17	A2	=A$1+COUNTA(C$2:C2)&IF(C2="","."&COUNTA(INDEX(D:D,LOOKUP("々",C$1:C1,ROW($1:1))):D2)&IF(E2="","","."&COUNTA(INDEX(E:E,LOOKUP("々",D$1:D1,ROW($1:1))):E2)),"")

 

[jasonb75]

Shorter again,

=A$1+COUNTA(C$2:C2)&IF(C2="","."&COUNTA(INDEX(D:D,MATCH("々",C$1:C1)):D2)&IF(E2="","","."&COUNTA(INDEX(E:E,MATCH("々",D$1:D1)):E2)),"")

 

[shaowu459]

Shorter again,

=A$1+COUNTA(C$2:C2)&IF(C2="","."&COUNTA(INDEX(D:D,MATCH("々",C$1:C1)):D2)&IF(E2="","","."&COUNTA(INDEX(E:E,MATCH("々",D$1:D1)):E2)),"")

Thank you~ I use lookup in the very begining and totally forgot MATCH

 

[jasonb75]

Easily done

I tried a few alternatives with things like TEXT(DATE(COUNTA(...))) which do work, but not as well. Too many limitations and a leading zero in one field.

I did notice one potential flaw in the shorter formulas. Using the example in post 13, enter some text into E18, then check the results of A17 and A20. Multiple entries against a single label with merged rows causes numbers to be missed. I think that your original suggestion in post 9 maintained the correct sequence.

 

[Bugalho]

Thank you so much guys!