Return "Next" when the row above in primary column is not blank and row below is blank

kharrison

Example based on the image: if column "ID" row 2 is not blank and column "ID" row 4 is blank, then column "Next" row 3 will say Next and all other cells will be blank. This will be ongoing so needs to be a column formula so I can assign the Next available license.

Note: I've been going at it for days using the community pages and tried various suggestions and deleted them all when they didn't work so I don't have any formulas or error messages to share. But I do appreciate your help and hope my explanation and image help tell the story.

Paul Newcome

First insert an auto-number column with no special formatting (called "Auto" in this example).

Then you will insert a text/number column (called "Row" in this example) with this column formula:

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

Finally in the Next column you would use:

=IF(AND(IFERROR(INDEX(ID:ID, Row@row - 1), "") <> "", ID@row = ""), "Next")

kharrison

Thank you Paul. Worked perfectly! Have a wonderful day.

kharrison

Hi @Paul Newcome , one more question. If I want to add another IF condition, what would that look like? So if the account was approved to get a license (Approved Column = Yes), then the above conditions you answered for me to return the Next column with "Next".

Not sure if we'll need this but I'd like to get ahead of it in case they decide this might be a condition that must be met later on.

Appreciate your suggested answer in my post and the others in the community. They have saved me a number of times.

Paul Newcome

What would be the logic and workflow? What if Approved in row 2 is not yes? What if Approved in Row 3 is not yes? How does the Approved column fit into the big picture?