INDEX MATCH Formula with row Hierarchy

SClever

Hello,

I need to reference information from the Digital column and Match it into the Digital Row. The row in white is the Parent column and the rows in grey are the Children. Here is the formula I'm using:

=(INDEX(PARENT(Digital@row), MATCH("Digital", CHILDREN(WO@row), 0)))

In the Parent row the cell is blank, but in the Children row it says #NO MATCH. I know I have to be close I'm just not sure what to try next.

Thank you!

Paul Newcome

What exactly are you wanting to accomplish? Are you trying to pull "Kadin" into the cell containing #NO MATCH?

Paul Newcome

That would look more like this:

=IF(COUNT(CHILDREN()) = 0, IF(WO@row = "Digital", PARENT(Digital@row), IF(WO@row = "Graphics", PARENT(Graphics@row), IF(WO@row = "Fab", PARENT(Fab@row)))), IF(WO@row = "Digital", Digital@row, IF(WO@row = "Graphics", Graphics@row, IF(WO@row = "Fab", Fab@row))))

Paul Newcome

With nesting, the only limit you should run into would be the limit of 4,000 characters per cell (including spaces).

Paul Newcome

What exactly are you wanting to accomplish? Are you trying to pull "Kadin" into the cell containing #NO MATCH?

SClever

Yes! Under the cell in the formula column and Digital row.

Paul Newcome

If you have the rows indented under row 1 then you should be able to just use

=PARENT(Digital@row)

SClever

Oh maybe I should clarify. I need this to pull for every corresponding child row for each order. so whatever value is in the parent row is reflected in the child row in the formula column.

The intention is to pull this information for a report so we can schedule per individual in the Gantt view. The report doesn't pull both the parent and child rows unless the information matches.

Paul Newcome

Ok. Try this as a column formula:

=IF(COUNT(CHILDREN()) = 0, PARENT(Digital@row), Digital@row)

SClever

Great, so that worked. Would I be able to have it be nested so I could pull information for each corresponding child row? So the formula would search the Digital@row, Graphics@row, Fab@row, etc. for values to pull and match to the corresponding child row.

Paul Newcome

That would look more like this:

=IF(COUNT(CHILDREN()) = 0, IF(WO@row = "Digital", PARENT(Digital@row), IF(WO@row = "Graphics", PARENT(Graphics@row), IF(WO@row = "Fab", PARENT(Fab@row)))), IF(WO@row = "Digital", Digital@row, IF(WO@row = "Graphics", Graphics@row, IF(WO@row = "Fab", Fab@row))))

SClever

Hey Paul, this worked! Do you know if there are any limits?

Paul Newcome

There could potentially be a number of limits. What exactly are you concerned about?

SClever

I was just wondering if it would have nesting limits. So far it seems to be working. Thank you for all of your help!

Paul Newcome

With nesting, the only limit you should run into would be the limit of 4,000 characters per cell (including spaces).