Pulling Parent Data without Grandparent in a column with a column formula
I would like to pull data from a parent row into the children but with a column formula it goes back to the grandparent, how do I exclude the grandparent data?
Best Answer
-
Ahh, I think I understand now. I would suggest the following for converting the parent of the Sunday column:
=IFERROR(DATE(VALUE(RIGHT(PARENT(Sunday@row), 4)), VALUE(LEFT(PARENT(Sunday@row), 2)), VALUE(MID(PARENT(Sunday@row), 4, 2))), "")
This column formula would go in the "Date Sun [HIDE]" column.
Note also, though, that this method of pulling portions of the text string to convert them to a date requires that dates in the Sunday text column be written as MM/DD/YYYY.
Does that make sense and work for what you need to accomplish?
Answers
-
Hope all is well. If you are just trying to pull data from the direct parent row, it will just pull the direct parent by using the PARENT( formula (i.e. =PARENT(Primary@row) ).
However, if you are using it in a different way, it may be helpful to set up an "Ancestors Count" column with a formula such as the following:
=COUNT(ANCESTORS(Primary@row))
Then, if you need to pull the direct parent, you can additionally condition based on the ancestor count on that row minus 1
-
Thank you so an IF statement referencing a column with the ancestor count data used to populate the column parent data?
-
Yeah, I think it depends on your exact use case. Are you able to share screenshots (with sensitive data redacted) and your current formulas? This would help give a picture of exactly what you are hoping to achieve.
-
This is for scheduling - the day of the week columns are text columns so we can enter that days schedule assignment. Each week of the year has a header row, i.e. the Parent row. If an editor adds a row within that week, I need a column formula to FORCE the hidden DATE columns to populate when a new row is added. Date Sun [HIDE] is the column I was attempting to add the =PARENT () formula but then all the children pull from the grandparent rows at the top.
-
FYI - the date columns are used to filter reports
-
Maybe I'm misunderstanding still. However, the =PARENT() function should work. The caveat there is that the row needs to be appropriately indented under the parent row. Are all rows added being indented under the the correct row?
If this still doesn't address your question, do you mind sharing the column names as well in your screenshot. I assume the grey date fields to the left are what is normally hidden? Is this where you want column formulas to pull parent row for each weekday (pulling date from those parent rows)?
-
Sorry, I thought the column names were visible. Here is a revised version. Yes, the gray columns are hidden and used to pull correct filtered info to a shared report. Each week's info is indented under a week's row. Once a week has passed it is indented a second time to fall under the main header row so it is no longer visible week to week.
-
The BOLDED dates are the week's PARENT row. The red dates are the dates I want to FORCE to use the parent row so that new rows will have the correct data. The gray date columns use a formula +1 day to get their date from the Date Sun [HIDE] column.
-
Ahh, I think I understand now. I would suggest the following for converting the parent of the Sunday column:
=IFERROR(DATE(VALUE(RIGHT(PARENT(Sunday@row), 4)), VALUE(LEFT(PARENT(Sunday@row), 2)), VALUE(MID(PARENT(Sunday@row), 4, 2))), "")
This column formula would go in the "Date Sun [HIDE]" column.
Note also, though, that this method of pulling portions of the text string to convert them to a date requires that dates in the Sunday text column be written as MM/DD/YYYY.
Does that make sense and work for what you need to accomplish?
-
THAT WORKED!!!!
Wow! Are you even a human? ha ha. That is one complicated string - I am so grateful for humans like you!
Thank you :-)
-
Awesome! I'm so glad we got it figured out:)
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!
