Auto fill

Alister

hello

I hope this message finds you well. I am currently working on a project where I have a sheet containing columns for tasks, locations, and tasks under different child categories. I am looking for assistance in creating a formula to automatically fill up the location column in the main row category with the corresponding location information.

If anyone has experience with a formula that achieves this or can provide guidance, I would greatly appreciate your help.

Thank you in advance for your support.

Regards

Alister

KPH

Is this what you are looking for? Or close?

Here I have put the parent text from the task column into the location column.

This is achieved using

=PARENT(Task@row)

Which if applied to the whole column would look like this:

You can add some IF functions to only do this for certain rows. For example, to make this display only for rows with 4 ancestors, you could do this.

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), "")

And to pull across the row with 3 ancestors, you could nest another IF, like this:

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), IF(COUNT(ANCESTORS(Task@row)) = 3, Task@row, ""))

I hope that is what you need.

KPH

Is this what you are looking for? Or close?

Here I have put the parent text from the task column into the location column.

This is achieved using

=PARENT(Task@row)

Which if applied to the whole column would look like this:

You can add some IF functions to only do this for certain rows. For example, to make this display only for rows with 4 ancestors, you could do this.

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), "")

And to pull across the row with 3 ancestors, you could nest another IF, like this:

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), IF(COUNT(ANCESTORS(Task@row)) = 3, Task@row, ""))

I hope that is what you need.

Alister

Nice one Thank you :)

Alister

KPH

Jan 27, 2024

Is this what you are looking for? Or close?

https://us.v-cdn.net/6031209/uploads/Z4G4ZDWYBHUQ/screen-shot-2024-01-26-at-20-59-54.png

Here I have put the parent text from the task column into the location column.

This is achieved using

=PARENT(Task@row)

Which if applied to the whole column would look like this:

https://us.v-cdn.net/6031209/uploads/EKZBFFZXR10O/screen-shot-2024-01-26-at-21-02-11.png

You can add some IF functions to only do this for certain rows. For example, to make this display only for rows with 4 ancestors, you could do this.

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), "")

https://us.v-cdn.net/6031209/uploads/7BQYGRCL2HSY/screen-shot-2024-01-26-at-21-06-50.png

And to pull across the row with 3 ancestors, you could nest another IF, like this:

=IF(COUNT(ANCESTORS(Task@row)) = 4, PARENT(Task@row), IF(COUNT(ANCESTORS(Task@row)) = 3, Task@row, ""))

https://us.v-cdn.net/6031209/uploads/6RS7P6AGB0HT/screen-shot-2024-01-26-at-21-09-26.png

I hope that is what you need.

Nice one Thank you :)

KPH

Glad I could help.