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]

Just tried closing and reopening it and it is working fine. If you want to put your workbook on a shared server eg drop box, google drive etc I am happy to take a look.

 

[valleyHemenway]

Thanks so much, here is a link:

test.xlsx

Shared with Dropbox

www.dropbox.com

 

[valleyHemenway]

Also, why do you avoid OFFSET, have you seen issues with compatibility or something else?

 

[Alex Blakenburg]

I have a large spreadsheet this is happening on.

I am still not having any issues with your original formula in terms of it refreshing the values.
Do you actually have the issue with the test file you sent me ?
It might be something to do with the size of your actual spreadsheet ?

Also the file you sent me was an XLSX, is your original file an XLSM or XLSB. If that is the case are there any event macros in the sheet or on opening the Workbook.

also you can try disabling any Add-Ins you have installed and active, to see if that makes a difference.
I will tackle the Offset question separately.

 

[valleyHemenway]

The original file and the test file are both XLSX files. No macros in them. The file is only 268K so not large. I have tried the original spreadsheet on more than one system and was having the issue. However, I am about to clean install the latest OS on my computer, so once I do that I will try the test spreadsheet again and see if I get the same results. Just for the heck of it though, I uploaded the test file to my OneDrive and it didn't have the issue I am having locally. I am wondering if it is a Mac Excel issue.

 

[jaeiow]

An alternative,

=LET(ab,BYROW([Column2],LAMBDA(x,IFNA(ROW(XLOOKUP(0,x,x,,,-1)),""))), IF([@Column2]=0,COUNT(FILTER(ab,ab<=ROW())),""))

 

[Alex Blakenburg]

Also, why do you avoid OFFSET, have you seen issues with compatibility or something else?

Indirect and Offset are 2 of 10 or so functions that are Volatile. In layman's terms what that means is that whereas other functions only recalculate when any of the cells that feed into it change, "volatile formulas get automatically recalculated any time you enter data anywhere in any open workbook" (per Chandoo - Link below)
Handle Volatile Functions like they are dynamite » Chandoo.org - Learn Excel, Power BI & Charting Online

Indirect is probably top of the list in terms of formulas to avoid, see Charles Williams link below and who is probably the most quoted source when it comes to Excel performance.
INDIRECT – Excel’s Most Evil Function

@jaeiow has given you another option which avoids both and here is yet another one:

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

Note: Also per Charles Williams, Conditional formatting also behaves in a manner similar to Volatile Functions, and if you have a lot of them in your spreadsheet that may also account for the "Indirect" refresh issue you are experiencing.

 

[jaeiow]

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]

This was my original intention with the Xlookup (last to first). Omits the byrow, which was adding no value. Doesn't/can't use solely structured reference, which is no good, but with a "speedy lookup" can calculate fairly quick.

=IF(ROW()=3,
        1,
LET(
  Cat, 0,
    IF([@Column2]=Cat,
          LET(
            ab,
                IFNA(XLOOKUP(Cat,
                E2:E$3,
                D2:D$3,,0,-1),""),
            ab + 1),
    ""))

 

[valleyHemenway]

Indirect and Offset are 2 of 10 or so functions that are Volatile. In layman's terms what that means is that whereas other functions only recalculate when any of the cells that feed into it change, "volatile formulas get automatically recalculated any time you enter data anywhere in any open workbook" (per Chandoo - Link below)
Handle Volatile Functions like they are dynamite » Chandoo.org - Learn Excel, Power BI & Charting Online

Indirect is probably top of the list in terms of formulas to avoid, see Charles Williams link below and who is probably the most quoted source when it comes to Excel performance.
INDIRECT – Excel’s Most Evil Function

@jaeiow has given you another option which avoids both and here is yet another one:

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

Note: Also per Charles Williams, Conditional formatting also behaves in a manner similar to Volatile Functions, and if you have a lot of them in your spreadsheet that may also account for the "Indirect" refresh issue you are experiencing.

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.