Conditional Format

[andreagiordano]

Hi there, 

I have two columns where drivers put km at the beginning and at the end of trip. I want to check when km are not imputed consecutively.  

In Google sheet I manage to do this with conditional formatting as in pic. 

I could set the initial value of km inizio with this expression Max(Calcolo costo[Km fine]) (I have this expression in Suggested value). I don't want user to be stuck here, if the previous driver has not imputed his trip or any other reason. But I would like to have the field highlighted in a view (as in the picture) just as an alert.  

On one hand this means also that I need the rows properly ordered ... I'm not sure how to do this in the original spreadsheet (actually I'm moving the rows manually). 

[JPAlpano]

You can do this in the Format Rules option of the Views button in the sidebar.

You will have to set the condition using an expression that checks the current entry with the last entry, maybe based on the input date/time?

[andreagiordano]

Yea,  I don't manage to find the correct expression. 

row1	76973	76977
row2	76980	76992
row3	76992	77048
row4	77048	77163
row5	77163	77175
row6	77175	77183

Wanted to say to appsheet "if km inizio in row 2 is <> from km fine in row 1 then format km inizio cell" and so on with row 3 , 4 ......

In the initial value I'm using the function max to get the biggest value imputed  but it doesen't works to solve the above problem.

[andreagiordano]

I'm trying to solve the thing above for which I'm trying to prepare an expression.... I want to ask appsheet to do this ... "check all the values in column A and find the same value in column B if you don't format the cell ....  

 in([km inizio],list([km fine]))
(The value of column 'km inizio') is one of the values in the list (LIST( ....The value of column 'km fine'))

Thsi expression gives me all NO .. so something is wrong.

Then I see that the format rules works in positive ... If the condition is true then format ... but I'm working on the other way around... If the condition is true do not format. I might use Not() for this ...

[1minManager]

Use MAXROW someting along the lines of this to get the milage from the previous row

MAXROW("TableName","Mileage",[KeyColumn]<>[_ThisRow].[KeyColumn])

https://support.google.com/appsheet/answer/10107920?hl=en

Call that column [LastMilage].  Then do something like this for the format rule

OR(
   [LastMilage]<[Mileage]-2,
   [LastMilage]>[Mileage]+2
)

Simon, 1minManager.com

[andreagiordano]

I already have a virtual column like "Lastmileage", where I use Max as expression (Max Tablename[Column name] ), but this gives me the max value of the entire column, and this is not useful.   

I tried this expression:
in([km inizio],list(tablename[km fine]))

In the example below the value 77048 of Km inizio is present in the list of km fine .... so I was expecting this to give me the right result (Yes) but it gives me NO (False) result. 

This is near to what I'm looking for I think but there must be some piece missing.

[andreagiordano · Accepted solution]

Oh my God ... it works Eureka Eureka ... i don't know why because the result of the testing of te expression looked odd to me (always yes) but then it does what I wanted ... 
I put two rules

• Not(in([km inizio],list(Calcolo Costo[km fine])))
• Not(in([km fine],list(Calcolo Costo[km inizio])))

and I got the result as below!

[andreagiordano]

• and([km inizio]<>Min(Calcolo costo[Km inizio]),(Not(in([km inizio],list(Calcolo Costo[km fine])))))
• and([km fine]<>Max(Calcolo costo[Km fine]),Not(in([km fine],list(Calcolo Costo[km inizio]))))

These are the two expressions ... that solved my idea. I have the formatting in both columns where ther is discontinuity in km registration, but also (with this bit [km inizio]<>Min(Calcolo costo[Km inizio]) the first value, the lowest one, and the last value, the highest one, don't get formatted! 

I'm so proud of it 😎🤣🤣🤣