Return value from another column for all Rows and set column to "column formula"

Ami Veltrie

This formula is producing syntax error when I try to make the column a "column formula". Is there a workaround?

As we add new Rows (copied from different sheet), I want all values in Client Name column to equal Task column Row 1.

Kelly P.

@Ami Veltrie If you want all rows that are indented under a given row (a row that's completely outdented) to have that row's client name, no matter how many levels of indentation are occurring under that given row, then try this:

=IFERROR(INDEX(ANCESTORS(Task@row), 1), "")

Here's an example of the results:

Kelly P.

@Ami Veltrie In the "Client Name" column, use the formula:

=PARENT(Test@row)

Then you'll be able to make the formula a column formula.

Hope this helps!

Ami Veltrie

@Kelly P. Thanks for the response! I tried this formula:

=PARENT(Task@row)

However, it's not working because we have more than two hierarchy levels. In other words it's not working for anything lower than level 2.

Kelly P.

@Ami Veltrie If you want all rows that are indented under a given row (a row that's completely outdented) to have that row's client name, no matter how many levels of indentation are occurring under that given row, then try this:

=IFERROR(INDEX(ANCESTORS(Task@row), 1), "")

Here's an example of the results:

Ami Veltrie

@Kelly P. Thank you!! That works!

Kelly P.

@Ami Veltrie Happy to help! 😊