Trying to combine 2 formulas into one, each formula with multiple conditions

spham5

Hi All,

I'm fairly new to formulas here on Smartsheet and have been racking my head over this one to no avail so far.

I basically have two formulas that I am trying to combine into one, and each formula has multiple conditions.

In the first formula, the conditions to be met are that if both columns of Date Received are not blank, another column for In-house testing is unchecked, and 4 columns following In-House testing remain blank, that the value returned is 1.

In the second formula, if the first two date columns are not blank, the in-house testing column is now checked, and the 4 columns following in-house testing is now NOT BLANK, that the value is then returned 1,

otherwise, I want all values to be returned 0.

So far, my formula is:

=IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 0), ISBLANK([In-House Test]@row), ISBLANK([In-House Submission QTY]@row), ISBLANK([In-House Submission Date]@row), ISBLANK([In-House Testing Completion Date]@row)), 1, IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 1), NOT(ISBLANK([In-House Testing Completion Date]@row)))), 1, 0)

With this formula, I'm getting INCORRECT ARGUMENT SET straight down the column of where I'm inputting this formula (to check boxes).

If I was to rearrange the formula this way:

=IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 0), ISBLANK([In-House Test]@row), ISBLANK([In-House Submission QTY]@row), ISBLANK([In-House Submission Date]@row), ISBLANK([In-House Testing Completion Date]@row)), 1, IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 1), NOT(ISBLANK([In-House Testing Completion Date]@row)), 1)))

I basically end up with the first half of the formula working (checkboxes for this first half appear and are checked), but the second IF statement in the second half now gives INCORRECT ARGUMENT SET still (checkboxes do not appear and instead remain to show INCORRECT ARGUMENT SET error).

AND if I rearrange the formula this way:

=IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 0), ISBLANK([In-House Test]@row), ISBLANK([In-House Submission QTY]@row), ISBLANK([In-House Submission Date]@row), ISBLANK([In-House Testing Completion Date]@row)), 1, AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 1), NOT(ISBLANK([In-House Testing Completion Date]@row)), 1))

by removing the second IF and just leading with AND(NOT(ISBLANK.... in the second half, the checkboxes where the INCORRECT ARGUMENT SET errors were now all appear, but again only the first half of the formula is working and the second half doesn't return the value of 1 (checking the box I want it to check).

If anyone can please help guide me on where I'm going wrong, it would be greatly appreciated! Thank you so much in advance everyone!

Carson Penticuff

See if this works. You have a lot going on it here...

=IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 0), ISBLANK([In-House Test]@row), ISBLANK([In-House Submission QTY]@row), ISBLANK([In-House Submission Date]@row), ISBLANK([In-House Testing Completion Date]@row)), 1, IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 1), NOT(ISBLANK([In-House Testing Completion Date]@row))), 1, 0))

Carson Penticuff

I'm glad it worked for you.

Carson Penticuff

See if this works. You have a lot going on it here...

=IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 0), ISBLANK([In-House Test]@row), ISBLANK([In-House Submission QTY]@row), ISBLANK([In-House Submission Date]@row), ISBLANK([In-House Testing Completion Date]@row)), 1, IF(AND(NOT(ISBLANK([Date CoA Received]@row)), NOT(ISBLANK([Date CoA Received (Test 2)]@row)), ([In-House Testing? ]@row = 1), NOT(ISBLANK([In-House Testing Completion Date]@row))), 1, 0))

spham5

@Carson Penticuff Thank you so much! I was able to use this and expand upon it. 😊

Carson Penticuff

I'm glad it worked for you.