Multiple Field True False using If(and(or)

[D_harrier]

Greetings,

I’m working on building a compliance audit tool, where one of the columns [Compliant] is marked ‘Yes or No’ using an expression that is based on the inputs from the previous questions.

The issue I am running into, is that one of the fields is optional, where not all users will see (based on geographic market). With this column hidden from some, my ‘If(and’ formula will not work correctly.

This is my current formula which works when all the questions are displayed:

if(and([Clean] = “yes”,[Labeled]=“yes”,[Funnel]=“yes”,[Containment]=“yes”,[CA Only]=“yes”),“yes”,“no”)

The issue, is when [CA only] is hidden, the formula will not work correctly, as the user can not select ‘Yes or No’. I was attempting to add a nested ‘Or’ statement, but am having issues with it working.

Any help is appreciated.

Thank you!

[LeventK]

@D.harrier, try with this

IF(
	AND(
		[Clean] = “yes”,
		[Labeled]=“yes”,
		[Funnel]=“yes”,
		[Containment]=“yes”,
		IFS(
			ISBLANK([CA Only]),“yes”, // assuming your initial value is blank when hidden from user
			TRUE,[CA Only]
		)
	),
	“yes”,
	“no”
)

[D_harrier]

LeventK:

IFS(
			ISBLANK([CA Only]),“yes”, // assuming your initial value is blank when hidden from user
			TRUE,[CA Only]
		)
	),
	“yes”,
	“no”
)

Looks like this is close, but I am still kicking an error. I removed “// assuming your initial value is blank when hidden from user”

When tested, the error ‘has an invalid structure: subexpressions must be yes/no conditions’

Thanks

[LeventK · Accepted solution]

Sorry, its totally my bad…Please try with this:

IF(
	AND(
		[Clean] = “yes”,
		[Labeled]=“yes”,
		[Funnel]=“yes”,
		[Containment]=“yes”,
		IFS(
			ISBLANK([CA Only]),TRUE,
			ISNOTBLANK([CA Only]),
			IF(
				[CA Only]=“yes”,
				TRUE,
				FALSE
			)
		)
	),
	“yes”,
	“no”
)

[D_harrier]

This worked perfectly. Thank you for your help!

[LeventK]

You’re welcome. Glad to be helped of.