Referencing Previous Cell and next cell in the same column

[Hiteshp]

Hello All,

I am trying to get an answer in a new column based on the reference in the previous cell and the current cell. The reference points are in the same column but the answer needed is in a new column. Like in the below example, if the Aux Changes from Avail to ACW it should show as toggle in the next column. so its ideally referencing the above cell and the cell below to give an output

Aux	Remark
Avail
ACW	Toggle
Avail
ACDIN
ACW
Avail
Personal	Toggle

[KGT]

The Multi-row Formula is what you're after.

Create a new field and the formula will be:

IF [Row-1:Aux]=="Avail" && [Aux]=="ACW" THEN "Toggle" ELSE Null() ENDIF

[Hiteshp]

Thank you, the formula works the way its intended to work. how about if i have to use multiple criteria like the second instance where apart from the first condition it should also fulfill the second condition 

[KGT]

I'm not sure what the second condition is.

Basically, this is your condition and so can be changed accordingly: 

• [Row-1:Aux]=="Avail" && [Aux]=="ACW"
• ([Row-1:Aux]=="Avail" && [Aux]=="ACW") OR ([Row-1:Aux]=="Something else" && [Aux]=="Another thing") OR [Row-1:Aux] != [Aux]

If you need something else in the THEN part, you would need an ELSEIF like

IF [Row-1:Aux]=="Avail" && [Aux]=="ACW"

THEN "Toggle"

ELSEIF [Row-1:Aux]=="Something"

THEN "AAnother thing"

ELSE Null()

ENDIF

[Hiteshp]

IF [Row-1:Aux]=="Avail" && [Aux]=="ACW" OR [Row-1:Aux]=="Avail" && [Aux]=="Personal" THEN "Toggle" ELSE Null() ENDIF

how do i correct the above formula to get the below output. i need to have multiple criteria to get the result.

Aux	Remark
Avail
ACW	Toggle
Avail
ACDIN
ACW
Avail
Personal	Toggle

[apathetichell]

I'd recommend seperating your logic from excel syntax to how it works (if these fields are like this - I would expect to see this) - not spreadsheet thought. This is probably an inner join - where when certain categories match a value in your data overwrites another join - but you may not get to that solution if you look at this with cell/spreadsheet based logic.

[Hiteshp]

IF [Row-1:Aux]=="Avail" && [Aux]=="ACW" OR [Aux]=="Personal" THEN "Toggle" ELSE Null() ENDIF

helps me get the output i need..

One more help i need is when i have "toggle" in the new column i need a third column which should get me the below output.

Aux	Remark	Reason
Avail
ACW	Toggle	Avail to ACW
Avail
ACDIN
ACW
Avail
Personal	Toggle	Avail to Personal
Avail
Deskwork	Toggle	Avail to Deskwork
Break
Tech	Toggle	Break to Tech

[apathetichell]

use multi-row formula to create an incremental grouping of where toggle occurs in a new field. use a tile tool to generate tile sequences based upon that index. use a summarize tool to group by that field - taking the min and max tile sequence.

use two join tools (or transpose) to match your toggle index and your min-max values to the specific tile sequences. This should link identify which toggle column matches which values in the avail column.

if you've transposed - you'll then use join in concat to concatentate value them with 'to" as your seperator.

If you've kept these are seperate fields use a formual tool to create your logic. 

[KGT · Accepted solution]

My formula from above will work for the toggle column, just keep adding clauses. If you have 50 clauses, then there's probably a better way than addressing them individually, but without knowing the whole issue, I can't say

IF [Row-1:Aux]=="Avail" && [Aux]=="ACW"

THEN "Toggle"

ELSEIF [Row-1:Aux]=="Avail" && [Aux]=="Personal"

THEN "Toggle"

ELSE Null()

ENDIF

And for the next column. A Multi-row with:

IF [Remark]="Toggle"

THEN [Row-1:Aux] + " to " + [Aux]

ELSE Null()

ENDIF