Numbering Parent Rows

Sherry Fox

@Jeff Reisman ,

Wondering one more thing. This now causes issues when I try to use the INDEX/MATCH in those orange highlighted squares. Is there anyway to indicate that every 6 rows (#1, #7, #13, #19) IS a parent? The numbering on the left works perfect but when I enter the INDEX/MATCH the whole thing gets messed up. (I am working on a copy)

Jeff Reisman

@Sherry Fox Can you share the INDEX/MATCH formulas you're using (in all the columns?)

That #CIRCULAR REFERENCE error in the Release Name column indicates that there's a formula in there that references the cell that it's in. Which error appeared first, that one or the #BLOCKED error in the Release column? I'm asking because the #BLOCKED error happens when a formula is trying to use data from a cell that has an error in it - such as [Release Name]7.

Sherry Fox

@Jeff Reisman ,

I do understand the "WHY" aspect of the circular reference. I initially set the sheet up where everything was formulas, and I had to manually enter the Release into those orange highlighted cell. However Now I am trying to adjust that part so I can have a list of the releases on one sheet and use that new RowID to do the INDEX/MATCH. So as a result, I need to change the formulas for some. So that is why I had asked if it would be possible to change the formula it is because I am now needed to "reverse-engineer" a few things. All my formulas used the Release column as a starting point. I KNOW that each parent will ONLY have 5 phases, there are no exceptions to this rule. So based on this, can anything be done to the RowID to where it is not looking for a parent? How about if it already knows that every 6th record ((#1, #7, #13, #19,...) will be a parent? and numbers the records the same way they are now? If we can accomplish that, I will no longer get the circular references and blocked errors. Thanks so much!

Jeff Reisman

@Sherry Fox You can use your formula within an IF statement, based on whether or not [Is Parent?]@row is blank, so that you can use one formula for Parent rows and another formula for Child rows.

=IF(ISBLANK([Is Parent?]@row), formula for child rows, formula for parent rows)

Would that work for you?

Sherry Fox

@Jeff Reisman ,

But that is the thing, the data all comes from the Release, which is where I want to add an INDEX/MATCH formula, and the current cause of my error. That is why I wondered if I could count every 6th row. They would not be "designated" as parent/child rows until after the release is entered.

Jeff Reisman

@Sherry Fox I don't know of an easy way to tell it to count every 6th row.

If all the data comes in based on the Release value, what are you using to make the INDEX/MATCH work? I don't have enough information here.

Sherry Fox

@Jeff Reisman ,

I thought I was looking for a miracle, thanks so much for all your help before!

Sherry Fox

Hey @Jeff Reisman ,

I found a Plan B to combine my new need and what you offered that I needed. Here are my formulas:

Auto Auto Numbering

Parent =IF(Helper@row = "Parent", Auto@row)

ID =IFERROR(RANKEQ(Parent@row, Parent:Parent, 1), "")

Helper - Last column to the very right, this is a Text field.

I hid the columns that are not relevant. I added the INDEX/MATCH formula to the Blue highlighted Cells in the Release column. This first screenshot is all formulas with exception of the final (Helper) column, which is in text and highlighted in Purple. The second sheet is my Release List. Rather than manually entering each Release into the first screenshot, the Release List lets me have a master list of releases, and the first sheet is updated completely by formula! Having that purple column in text is a small price to pay for the automation!

Jeff Reisman

@Sherry Fox Excellent!