Relative Parent Function

Brandon Zinninger

I have an estimate sheet that has different parent categories "rows". Under these rows we have different child rows that contain products "child rows". The parent category has a check box that if active the child rows will multiply two cells in the same child row. Unfortunately I don't want to use the specific cell reference since this order sheet is constantly changing based on the different products we have available.

I'd like to dynamically have a formula that calls the parent row and checks the value of the checkbox column and if true executes the multiplication of the cells in the child row. I've tried doing this with the "parent()" function but no luck and I have no other ideas on how to get this done.

All help is greatly appreciated. :)

Paul Newcome

Are you able to provide a screenshot with manually entered data that shows what you are trying to accomplish?

Brandon Zinninger

@Paul Newcome the image is below. I'm trying to add the formula in the red circled number at the bottom. It needs to check the red circled checkbox at teh top. and if the checkbox is active it should multiply the blue circled button with another static field that is off screen.

Paul Newcome

Is the row with the numbers circled a child of the "Zipdee" row?

Brandon Zinninger

Yes

L_123

=if(index(ancestors(checkbox@row),1)=1,Blue@row*Static#

Paul Newcome

@L@123 That's what I was thinking too, but it looks like the checked row is also a child row of something above it. If that higher row is checked, then it will run off of that one even if the "7000 Awning" row is not checked.

I was thinking something along the lines of a helper column to establish hierarchy level then using an INDEX/COLLECT to look at the more immediate ancestors, but then rolling right into the IF statement with it.

@Brandon Zinninger Is the "7000 Awning" row also a child row of something else not pictured in your screenshot?

Brandon Zinninger

@L@123 If I understand the formula the "checkbox@row" checks the immediate parent row, its not checking the main parent row.

Also correct me if I'm wrong but the "blue@row" is the blue circle on the screenshot and the Static# is for the offscreen variable. Right?

I just want to confirm since it doesn't show the multiplied numbers even if the parent and main parent rows are active.

Thanks :D

L_123

If it is the second level then you just need to change the 1 in the index to a 2. Ancestors collects from the top down, so as long as it is consistent from the top, it doesn't matter how far the bottom is, you can just adjust the reference accordingly.

L_123

@Brandon Zinninger

Checkbox@row is the far left column where the checkbox you are checking is located

Blue@row is the blue circled cell

Static# is the referenced offscreen static cell

As I mentioned (i think we posted at the same time) you need to change the index in the cell to the level the checkbox falls on if the checkbox isn't at the highest parent.

Brandon Zinninger

That appears to work! Thank you so much!!!!

Brandon Zinninger

@L@123 One last question, if I wanted to use this same formula for the immediate parent row would I just change the index column name and keep the index number at 1?

L_123

no, that would take the highest level parent on the sheet instead of the immediate parent if I understand correctly. Instead you can just get rid of the index and replace ancestors with parent.

=if(parent(checkbox@row)=1,Blue@row*Static#

Brandon Zinninger

I see, Thanks @L@123 !

L_123

NP Glad I could help.