Relative Parent Function
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. :)
Answers
-
Are you able to provide a screenshot with manually entered data that shows what you are trying to accomplish?
-
@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.
-
Is the row with the numbers circled a child of the "Zipdee" row?
-
=if(index(ancestors(checkbox@row),1)=1,Blue@row*Static#
-
@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?
-
@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
-
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.
-
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.
-
That appears to work! Thank you so much!!!!
-
@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?
-
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#
-
I see, Thanks @L@123 !
-
NP Glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!