I want to create an auto-number for each row in my google sheet following the logic below:
- it takes the sheet number from A1 e.g. 8
- generates sequential table header numbers from that sheet number for each table header row e.g. first table 80.00 then 81.00 then 82.00 etc
- within each table the line items should be 80.01 then 80.02 then 80.03 etc for table 80.00 and 81.01 then 81.02 etc for table 81.00
- the numbers should not increment whenever the cell on the same row in B is blank
- to detect a table header we have a recurring value in column C that is constant for table headers and is "x QTY"
- the numbers should automatically update when users insert values and/or rows and/or delete values in column B
I have the formula that I can drag down from A2 and all works fine, but whenever I add a row the formula doesnt get copied into it, so i want to create an ARRAYFORMULA to fill the whole row according to the conditions above. This is my formula in A2 that I can drag down and works:
=IF(B2 = "", "", IF(C2 = "x QTY", IF(INT(MAX($A$1: $A1)) <= $A$1, $A$1 * 10, INT(MAX($A$1: $A1)) + 1), MAX($A$1: $A1) + 0.01))
I have tried to transpose this to the following ARRAYFORMULA but it just does not increment the way I want:
=ARRAYFORMULA(IF(B2:B = "", "", IF(C2:C = "x QTY", IF(INT(MAX(A$1:INDEX(A:A, ROW()-1))) <= $A$1, $A$1 * 10, INT(MAX(A$1:INDEX(A:A, ROW()-1))) + 1), MAX(A$1:INDEX(A:A, ROW()-1)) + 0.01)))
Anyone know why this is not working? I am in Google Sheets and not Excel. Thanks for your help
| 10.00 | New Table - Rename | x QTY |
|---|---|---|
| 10.01 | item 1 | 1 |
| (empty) | ||
| 10.02 | item | 1 |
| 11.00 | New Table - Rename | x QTY |
| (empty) | ||
| 11.01 | Item 2 | 1 |
| 11.02 | Item 3 | 1 |
| 12.00 | New Table - Rename | x QTY |
| 12.01 | Item 1 | 1 |
| 12.02 | Item 2 | 1 |
| 12.03 | Item 3 | 1 |
I want to create an auto-number for each row in my google sheet following the logic below:
- it takes the sheet number from A1 e.g. 8
- generates sequential table header numbers from that sheet number for each table header row e.g. first table 80.00 then 81.00 then 82.00 etc
- within each table the line items should be 80.01 then 80.02 then 80.03 etc for table 80.00 and 81.01 then 81.02 etc for table 81.00
- the numbers should not increment whenever the cell on the same row in B is blank
- to detect a table header we have a recurring value in column C that is constant for table headers and is "x QTY"
- the numbers should automatically update when users insert values and/or rows and/or delete values in column B
I have the formula that I can drag down from A2 and all works fine, but whenever I add a row the formula doesnt get copied into it, so i want to create an ARRAYFORMULA to fill the whole row according to the conditions above. This is my formula in A2 that I can drag down and works:
=IF(B2 = "", "", IF(C2 = "x QTY", IF(INT(MAX($A$1: $A1)) <= $A$1, $A$1 * 10, INT(MAX($A$1: $A1)) + 1), MAX($A$1: $A1) + 0.01))
I have tried to transpose this to the following ARRAYFORMULA but it just does not increment the way I want:
=ARRAYFORMULA(IF(B2:B = "", "", IF(C2:C = "x QTY", IF(INT(MAX(A$1:INDEX(A:A, ROW()-1))) <= $A$1, $A$1 * 10, INT(MAX(A$1:INDEX(A:A, ROW()-1))) + 1), MAX(A$1:INDEX(A:A, ROW()-1)) + 0.01)))
Anyone know why this is not working? I am in Google Sheets and not Excel. Thanks for your help
| 10.00 | New Table - Rename | x QTY |
|---|---|---|
| 10.01 | item 1 | 1 |
| (empty) | ||
| 10.02 | item | 1 |
| 11.00 | New Table - Rename | x QTY |
| (empty) | ||
| 11.01 | Item 2 | 1 |
| 11.02 | Item 3 | 1 |
| 12.00 | New Table - Rename | x QTY |
| 12.01 | Item 1 | 1 |
| 12.02 | Item 2 | 1 |
| 12.03 | Item 3 | 1 |
- Welcome to StackOverflow! Please provide a minimal reproducible example in the form of markdown tables containing your sample input data and desired output. You may create one with the help of this link. – PatrickdC Commented Jan 31 at 15:55
1 Answer
Reset to default 0Here's one approach you may test out; Cell_A1 is marked 1 here:
=map(C2:C,lambda(Σ,if(Σ="",,let(Λ,"x QTY",countif(C2:Σ,Λ)-1+A1*10 + if(Σ<>Λ,(counta(xlookup(Λ,C2:Σ,C2:Σ,,,-1):Σ)-1)*0.01,)))))