valleyHemenway
New Member
- Joined
- Nov 17, 2022
- Messages
- 11
- Office Version
- 365
- Platform
- MacOS
Hello,
My goal is to write a formula I can use in a table that creates an "ID" number sequence down the table. I skip some rows depending on values in other columns. So the first row will always have ID 1 and then it might be a few rows before I would expect ID 2 to show up and then eventually 3 and so on.
I do not want to use any specific references (A3). I have what seems to to be a workable formula, but it also acts like there is a circular reference. I have to click into any cell and press ENTER or press F9 before the values change to what they are supposed to be. There is no circular reference error in Excel, and I don't see how my formula creates one. I am stumped.
Does anyone know if I am actually creating a circular reference? Can anyone come up with a different way of creating my "auto-numbering" feature?
Thanks so much!
My goal is to write a formula I can use in a table that creates an "ID" number sequence down the table. I skip some rows depending on values in other columns. So the first row will always have ID 1 and then it might be a few rows before I would expect ID 2 to show up and then eventually 3 and so on.
I do not want to use any specific references (A3). I have what seems to to be a workable formula, but it also acts like there is a circular reference. I have to click into any cell and press ENTER or press F9 before the values change to what they are supposed to be. There is no circular reference error in Excel, and I don't see how my formula creates one. I am stumped.
Does anyone know if I am actually creating a circular reference? Can anyone come up with a different way of creating my "auto-numbering" feature?
Thanks so much!
test.xlsx | |||||||
---|---|---|---|---|---|---|---|
C | D | E | F | G | |||
3 | |||||||
4 | Column1 | Column2 | Column3 | ||||
5 | 1 | 0 | |||||
6 | A | ||||||
7 | B | ||||||
8 | C | ||||||
9 | 1 | 0 | |||||
10 | A | ||||||
11 | B | ||||||
12 | 1 | 0 | |||||
13 | A | ||||||
14 | 1 | 0 | |||||
15 | 1 | 0 | |||||
16 | 1 | 0 | |||||
17 | A | ||||||
18 | B | ||||||
19 | 1 | 0 | |||||
20 | 1 | 0 | |||||
21 | A | ||||||
22 | 1 | 0 | |||||
23 | A | ||||||
24 | B | ||||||
25 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D5:D24 | D5 | =IF(ISTEXT([@Column2]), "", MAX(INDIRECT(ADDRESS(ROW(Table1[#Headers]),COLUMN([Column1])) & ":" & ADDRESS(ROW()-1,COLUMN([Column1]))))+1) |