Another formula question

Mad · 05/24/22

Please could someone help to show me where I have gone wrong? I have been through this many times and cannot see why I am returning "Incorrect Argument Set".

=IF(AND([Column Name]@row = "Link Active & Works", [Column Name]@row = "Link Active & Works", [Column Name]@row = "Link Active & Works", [Column Name]@row = "Link Active & Works", [Column Name]@row = "Link Active & Works", [Column Name]@row = "Link Active & Works", [Column Name]@row = "Data Supplied", [Column Name @row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name]@row = "Data Supplied", [Column Name ]@row = "Data Supplied", "Green", =IF(AND([Column Name]@row = "Link Not Active", [Column Name]@row = "Link Not Active", [Column Name]@row = "Link Not Active", [Column Name]@row = "Link Not Active", [Column Name]@row = "Link Not Active", [Column Name]@row = "Link Not Active", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name]@row = "Data Missing", [Column Name ]@row = "Data Missing", "Red", "Yellow"))))

Thank you in advance for any answers

AnthroTim · 05/24/22

Hi Mad,

If we format out the Formula we can see a few things:

=IF(
	AND(
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Data Supplied",
		[Column Name @row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name ]@row = "Data Supplied",
		"Green",
		=IF(
			AND(
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Link Not Active",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name]@row = "Data Missing",
				[Column Name ]@row = "Data Missing",
				"Red",
				"Yellow"
				)
			)
		)
	)

Firstly your second IF has an = in front of it.

Secondly, your closing brackets look to be in the wrong order as the second IF is inside the first AND etc.

More like this:

=IF(
	AND(
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Link Active & Works",
		[Column Name]@row = "Data Supplied",
		[Column Name @row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name]@row = "Data Supplied",
		[Column Name ]@row = "Data Supplied"
	),
	"Green",
	IF(
		AND(
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name ]@row = "Data Missing"
		),
		"Red",
		"Yellow"
	)
)

Also, and I'm not sure if this is intentional, but all your Column Names are the same?

Hope that's helpful!

Tim

Mad · 05/24/22

https://community.smartsheet.com/discussion/comment/330783#Comment_330783

@AnthroTim Thank you for the answer I have used your corrections and it works which is fantastic you've really helped me. I like the way you have broken down the text it is a lot easier to read than the way I pasted it.

I have had to change all Column names to the same to protect the information I am working on. I really appreciate your help on this, I think this community function is amazing and I've received some excellent help with my problems so far.

Is there a way of writing an OR into this function as I didn't realise two of my columns have a third range of N/A which I would still like to return "Green" if the two cells have "N/A and all other cells return the values already written for the return of "Green"?

AnthroTim · 05/24/22

Hi @Mad,

No worries.

As for combining AND/OR. You can use an AND/OR as a logical operator inside an AND or an OR. So you can build up logic.

I think this will do what you want - it's hard writing it blind :)

The First OR acts to say that either of the following statements can be TRUE. Then there are two AND statements that combine the checks so that all of the statements inside the AND must be TRUE.

=IF(
	OR(
		AND(
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied"
		),
		AND(
			[Column Name]@row = "N/A",
			[Column Name]@row = "N/A"
		)
	   )
	),
	"Green",
	IF(
		AND(
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing"
		),
		"Red",
		"Yellow"
	)
)

Kindest Regards

Tim

Mad · 05/25/22

https://community.smartsheet.com/discussion/comment/330833#Comment_330833

@AnthroTim Thank you for your help however I cannot get this to work and I've used your syntax . I have also tried playing around with the IF(OR(AND written IF(AND(OR, I removed the N/A from the first IF(OR(AND then left them in the second AND( and tried adding all the columns with N/A after the second AND( the but I cannot get it to work I return #UNPASEABLE or #CIRCULAR REFERENCE

AnthroTim · 05/25/22

Hi @Mad,

Apologies - there was a typo in my formula (an extra bracket). 🙁

=IF(
	OR(
		AND(
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Link Active & Works",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied",
			[Column Name]@row = "Data Supplied"
		),
		AND(
			[Column Name]@row = "N/A",
			[Column Name]@row = "N/A"
		)
	   ),
	"Green",
	IF(
		AND(
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Link Not Active",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing",
			[Column Name]@row = "Data Missing"
		),
		"Red",
		"Yellow"
	)
)

I quickly knocked up a simplified test and the formula parses correctly - so should be good to go.

Tim

Mad · 05/27/22

https://community.smartsheet.com/discussion/comment/331077#Comment_331077

@AnthroTim Thank you again for persevering I don't understand what I'm doing wrong. This is the live formula I have written and checked multiple times and still can't see where I've gone wrong. If I have a column with "N/A" selected it still shows "Yellow" not "Green"

=IF(OR(AND([Column1]@row = "Link Active & Works", [Column2]@row = "Link Active & Works", [Column3]@row = "Link Active & Works", [Column4]@row = "Link Active & Works", [Column5]@row = "Link Active & Works", [Column6]@row = "Link Active & Works", [Column7]@row = "Data Supplied", [Column8]@row = "Data Supplied", [Column9]@row = "Data Supplied", [Column10]@row = "Data Supplied", [Column11]@row = "Data Supplied", [Column12]@row = "Data Supplied", [Column13]@row = "Data Supplied", [Column14]@row = "Data Supplied", [Column15]@row = "Data Supplied", [Column16]@row = "Data Supplied"), AND([Column4]@row = "N/A", [Column12]@row = "N/A", [Column15]@row = "N/A")), "Green", IF(AND([Column1]@row = "Link Not Active", [Column2]@row = "Link Not Active", [Column3]@row = "Link Not Active", [Column4]@row = "Link Not Active", [Column5]@row = "Link Not Active", [Column6]@row = "Link Not Active", [Column7]@row = "Data Missing", [Column8]@row = "Data Missing", [Column9]@row = "Data Missing", [Column10]@row = "Data Missing", [Column11]@row = "Data Missing", [Column12]@row = "Data Missing", [Column13]@row = "Data Missing", [Column14]@row = "Data Missing", [Column15]@row = "Data Missing", [Column16]@row = "Data Missing"), "Red", "Yellow"))

Mad · 05/27/22

I have also tried

=IF(OR(AND([Column1]@row = "Link Active & Works", [Column2]@row = "Link Active & Works", [Column3]@row = "Link Active & Works", [Count If]@row = "Link Active & Works", [Column5]@row = "Link Active & Works", [Column6]@row = "Link Active & Works", [Column7]@row = "Data Supplied", [Column8]@row = "Data Supplied", [Column9]@row = "Data Supplied", [Count If]@row = "Data Supplied", [Column11]@row = "Data Supplied", [Column12]@row = "Data Supplied", [Column13]@row = "Data Supplied", [Column14]@row = "Data Supplied", [Column15]@row = "Data Supplied", [Column16]@row = "Data Supplied"), AND([Column1]@row = "N/A", [Column3]@row = "N/A", [Count If]@row = "N/A", [Column6]@row = "N/A", [Column7]@row = "N/A", [Column11]@row = "N/A", [Column12]@row = "N/A", [Column14]@row = "N/A", [Column15]@row = "N/A", [Column16]@row = "N/A")), "Green", IF(AND([Column1]@row = "Link Not Active", [Column2]@row = "Link Not Active", [Column3]@row = "Link Not Active", [Count If]@row = "Link Not Active", [Column5]@row = "Link Not Active", [Column6]@row = "Link Not Active", [Column7]@row = "Data Missing", [Column8]@row = "Data Missing", [Column9]@row = "Data Missing", [Count If]@row = "Data Missing", [Column11]@row = "Data Missing", [Column12]@row = "Data Missing", [Column13]@row = "Data Missing", [Column14]@row = "Data Missing", [Column15]@row = "Data Missing", [Column16]@row = "Data Missing"), "Red", "Yellow"))

Another formula question

Answers

Help Article Resources

Categories