Need help with an expression

expressions
(Mic L. Angelo) #1

Can someones help me work out a code expression?

I’m trying to write a code for an action button that will only make the button appear if the value of two concatenated columns (let’s call them A & B) in the same row equal “Check Complete”

Can anyone help with that?

(Aleksi Alkio) #2

Try with AND([ColA]=“Check Complete”,[ColB]=“Check Complete”)

(Mic L. Angelo) #3

Thanks, Aleksi. I’ for something more along the lines like

NOT(IN(“Check Complete”, CONCATENATE(Sheet1[ColA], " ", Sheet1[ColB])))

(This does not work)

Logically, it would mean

" I only want to see the button if the sheet1 doesn’t have a row in which the concatenated value of [ColA] & [ColB] equals “Check Complete” "

Is it possible to write a function like that?

(Steve Coile) #4

Use CONTAINS() rather than IN().

(Mic L. Angelo) #5

Thanks Steve, unfortunately, I’m still having a bit of trouble.

This is what i came up with

NOT(CONTAINS(CONCATENATE([ColA], " ",[ColB]), “Check Complete”))

Which works, but only for a specific row. How can i make this rule apply to the entire sheet1?

The logic i want is “if the concatenated columns of one row in the sheet equal “Check Complete”, this condition is false for all the rows.”

How can i implement that logic?

(Steve Coile) #6

Ah, okay.

ISNOTBLANK(FILTER("MyTable", ...))

where MyTable is the name of the table and ... is your NOT(CONTAINS(...)) expression. The idea here is to look through the table and identify the rows that match your criteria.

See also: FILTER(), ISNOTBLANK()

(Mic L. Angelo) #7

Got this to finally work the way i need, had to re-configure the code, looks like this now

ISBLANK(FILTER(sheet1,
(CONTAINS(CONCATENATE([ColA], " ",[ColB]),
“Check Complete”))))

Thanks for the help Steve and Aleksi. Appreciated

1 Like