Confused On Circular Reference

[valleyHemenway]

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!

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)

 

[valleyHemenway]

My formula is not reliable. I'm not sure how to write a lambda-type of function in a table without scanning the whole column, making it inefficient.

jaeiow, thanks for contributing! I am not able to use the second one as one of the rules I have for this workbook is I can't use specific cell references in my formulas. I am not sure why, but that is one of the rules. Another one is no macros. Another one is getting all Conditional Formatting approved before use (which now makes more sense ).

What makes your first formula not reliable? Anyway, I appreciate the time and energy!

 

[Alex Blakenburg]

Thanks for the wonderful explanation! I do have a couple conditional formatting rows setup in the main excel sheet, but since it is also happening on my test file which doesn't, I think it likely has something to do with either Excel for Mac or Excel for Mac on M1. Either way, while not super ideal, the OFFSET option is working and is not causing the issue I was seeing before. After I reload and test again, I will report back. I will also see if I can test it on a Windows computer with Excel and see how that performs.

Appreciate the feedback and do let us know if you find out any more with your further testing.