Is there a way to reference the row above?

Amanda Merrigan

I'm trying to mark active child tasks in an overall task. I'd like do =IF(Done@row-1 = 1, 1, 0) in the Active Task column, but we often insert tasks and I don't want to put a hard @row number in there that would need to be maintained when new tasks are added. Same for automatic numbers in a RowID column.

I was also doing this based on status, but we have several subtasks that have the same status and we only want to add additional status' as a last resort.

Does anyone know of a way to do this with Ancestors or a helper column to show when the task above is complete and that is now the active task?

Paul Newcome

Insert an auto-number column (called "Auto) that has no special formatting.

Next insert a text/number column (called "Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you can incorporate this into your formula

Row@row - 1 will always give the row above regardless of sorting, deleting, or adding rows.

Paul Newcome

Insert an auto-number column (called "Auto) that has no special formatting.

Next insert a text/number column (called "Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you can incorporate this into your formula

Row@row - 1 will always give the row above regardless of sorting, deleting, or adding rows.

Amanda Merrigan

Amazing!! Thanks @Paul Newcome! This is so helpful!

Paul Newcome

Happy to help. 👍️

David Clunie

Hi

Sorry to go into an old post but this is what I want, but to look at the dates.

I want to look at moving rows which have sequential dates based on planning.

=WORKDAY([Online Date]248, 1, {Holidays Range 2})

I also want this to inherit from the date above to repopulate the sequence.

Currently, if we move a row we have to rebuild and pull down all online date formulas to rectify.

RKLRpp

cbredehoeft

where in the formula do you incorporate the added Row@row - 1 ?

Paul Newcome

@cbredehoeft It depends on the formula you are currently using.

cbredehoeft

=MATCH(Auto@row, Auto:Auto, 0)

Paul Newcome

@cbredehoeft Row@row - 1 would not be incorporated into that formula. That formula is used in the [Row] column to output the row number on the sheet so that it can be leveraged by other formulas. These other formulas are where you would incorporate Row@row - 1.