Pulling Parent Data without Grandparent in a column with a column formula

stkatch

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?

brianschmidt

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?

brianschmidt

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

stkatch

Thank you so an IF statement referencing a column with the ancestor count data used to populate the column parent data?

brianschmidt

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.

stkatch

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.

stkatch

FYI - the date columns are used to filter reports

brianschmidt

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)?

stkatch

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.

stkatch

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.

brianschmidt

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?

stkatch

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 :-)

brianschmidt

https://community.smartsheet.com/discussion/comment/427812#Comment_427812

Awesome! I'm so glad we got it figured out:)