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)

 

[Alex Blakenburg]

Would this work for you ?
In D5

=IF(ISTEXT([@Column2]), "", MAX(D$4:D4)+1)

 

[valleyHemenway]

Unfortunately no, because it is using a specific reference to a cell D4

 

[Alex Blakenburg]

Does that matter ? If you move the table the reference will move as well. What is the downside ?

 

[valleyHemenway]

I am working on this project with a couple people and that was part of the criteria I was given. I can certainly ask, but it might not be something I will get a quick answer on. What I don't understand is why it is acting like a circular reference. I am essentially doing what you are doing, just with ADDRESS and INDIRECT, correct?

 

[Alex Blakenburg]

I am not sure why it is behaving like a circular reference. The below is probably a more direct way of doing what you are doing:

=IF(ISTEXT([@Column2]), "", MAX(Table1[[#Headers],[Column1]]:OFFSET([@Column1],-1,0))+1)

I personally try to avoid INDIRECT and OFFSET wherever possible.

 

[Alex Blakenburg]

I just remembered to test for inserting and deleting rows, using D4 will break while offset will keep working

 

[valleyHemenway]

Thank you for the suggestion! I think using OFFSET will work. I am frustrated by why the other method isn't working, but sometimes you just go with what works

Thanks for the assistance!

 

[Alex Blakenburg]

I am not experiencing the issue you are having with your initial formula. Are you sure calculations are set to automatic and that you don't have any change events running ?

 

[valleyHemenway]

Yes, they are set to automatic. I have a large spreadsheet this is happening on. When I built this sample spreadsheet on a different computer the problem came over with it. The issue I have is when I first open the sample spreadsheet, all of the ID columns have a value of 1 until I either press F9 or go into any cell and then press enter. Then the ID's show up properly. Are you saying that when you build this spreadsheet, save, close, and re-open, the ID values are not all 1?