Populating parent and child rows with the same data
I am trying to pull data from the parent row in column "helper VRN" into both the parent and child cells in column "Verification Request Number" - I need to use a single formula because it needs to work for all rows, which may be pasted or added via a form, so I want to make it a column formula. This is the formula I have tried
=IF(COUNT(CHILDREN([helper VRN]@row)) = 0, PARENT([helper VRN]@row), [helper VRN]@row)
If this, (COUNT(CHILDREN([helper VRN]@row)), equals "0" the formula will populate the child rows. If it equals "1" the formula will populate the parent row but I can't get it to populate both parent and child.
Can someone tell me what I am doing wrong? I would appreciate any help on this.
Thanks
Best Answer
-
Hi @Tamsin
Your formula is working correctly! In your screen capture, there are blank cells in helper VRN column for the child rows. This means the COUNT of the CHILDREN in that specific column is 0, since there is no data in those child rows.
Instead of referencing your helper column in the first COUNT, make sure you're referencing a column that will always contain text or data when a row is submitted. For example:
=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([helper VRN]@row), [helper VRN]@row)
Does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
this is a screenshot of my sheet.
-
Hi @Tamsin
Your formula is working correctly! In your screen capture, there are blank cells in helper VRN column for the child rows. This means the COUNT of the CHILDREN in that specific column is 0, since there is no data in those child rows.
Instead of referencing your helper column in the first COUNT, make sure you're referencing a column that will always contain text or data when a row is submitted. For example:
=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([helper VRN]@row), [helper VRN]@row)
Does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much, that worked perfectly!
-
No problem at all! I'm glad I could help.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Do you have any input for solving this when grand-children are involved? I need to be able to pull parent level data down through multiple levels and am struggling with some inelegant formulas and helper columns that cannot be converted into Column Formulas.
-
Hi @Todd Lyon
You could use JOIN and ANCESTORS to bring multiple rows worth of data into one cell, if that would help!
For example, if I have a sheet like this:
You could use =JOIN(ANCESTORS([Primary Column]@row), " / ") to bring in the Parent and the Grandparent information into each Child cell:
Would this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!