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.

Jeff Fike

https://community.smartsheet.com/discussion/comment/396971#Comment_396971

But how? Can you give some examples so we can see it in context?

Paul Newcome

@Jeff Fike There are so many different ways and variations, we would need more context from you. What is it you are trying to do?

Jeff Fike

https://community.smartsheet.com/discussion/comment/462378#Comment_462378

Assuming column A

If ancestor = 2, set Ax = Ax-1 + 0.1 (Where x = row number; and x-1 is the cell above it in the same row)

So that would mean when I have

A3 = 1 (Ancestor =1 in this case)

A4 = 1.1

A5 = 1.2

etc. for as long as A4…Az is indented (i.e. Ancestor = 2)

I essentially want it to autonumber when ancestor =2 based off of whatever number was above it

Paul Newcome

@Jeff Fike It sounds like you are looking for a WBS solution. If you do a search here in the Community, there is one in particular that utilizes column formulas. That is currently the most relevant.

Jeff Fike

I can look into it but I thought that: -set current row equal to (row-1)'s value + x which is what we were discussing here?

Additionally:

1) I will need this functionality more than just the situation I offered and it won't always be a WBS situation.

2) An example on how to use what you offered would benefit more than just me if displayed/discussed here.

Paul Newcome

@Jeff Fike Yes. Technically it is, but the explanations here are very simplistic compared to a WBS which is what you are looking for. Instead of trying to reinvent the wheel here, it is better to just reference the original documentation as the WBS system that you are looking for requires a few helper columns and additional formulas.