# Attempting to populate the value of a cell based on the value of a different cell

Options
✭✭✭✭

So, the question posed below seemed very similar to what I'm trying to accomplish, and I did make some headway...until I started including multiple selection possibilities.

I want my "Pathways" column to assign one of five values based on the value of the "Discipline" column on the same row. There are 40+ Discipline values to choose from.

The end goal is for the form submitter to provide the "Discipline" from a drop-down and for the sheet to populate the "Pathways" cell based on that "Discipline."

For instance:

IF DISCPLINE = X, PATHWAY = Y, where more than one "Discipline" can belong to a single "Pathway."

ACCT, ACNT, BMGT [Discipline] = Business & Industry [Pathway]

ARTS, DANC, DRAM = Arts & Humanities

ANTH, EDUC, PSYC = Human & Public Service

CHEM, BIOL, MATH = STEM

I made the most headway with =IF(OR statements, until things broke. I've also looked some into =INDEX/MATCH, but...I'm at a loss.

THANKS!

Reference question

• ✭✭✭✭✭✭
Options

You are forgetting to close out the OR statements.

=IF(OR(.......), "Business Industry", IF(OR(.......), "Human & Public Service"

• ✭✭✭✭✭✭
Options

Are you able to post the formula that you had working along with the formula that broke?

• ✭✭✭✭
edited 04/25/23
Options

Hi, Paul! Thanks for asking - full disclosure, it partially works. As is, this only partially accomplishes what I'm trying to do. I still have more [DISCIPLINES] that need to be applied to one of the five [PATHWAYS]. The formula below has been applied to the entire PATHWAYS column and works-ish (i.e. no syntax or other errors).

=IF(OR(Discipline@row = "ACCT", Discipline@row = "ACNT", Discipline@row = "BCIS", Discipline@row = "BMGT", Discipline@row = "BUSG", Discipline@row = "BUSI", Discipline@row = "ECON", Discipline@row = "HIST", Discipline@row = "MRKG", Discipline@row = "POFI", Discipline@row = "POFT"), "Business & Industry", IF(Discipline@row = "ARTS", "Arts & Humanities", IF(Discipline@row = "ANTH", "Human & Public Service", IF(Discipline@row = "CHEM", "STEM", IF(Discipline@row = "N/A - Staff/Admin", "Staff/Admin")))))

• ✭✭✭✭✭✭
Options

• ✭✭✭✭
Options

Thanks for sticking with me, Paul!

I have abandoned the code for previous broken attempts, but the simplest example (which I tested to confirm) would be below. I'm sure I'm making a mistake, just not sure how to resolve it. Note the attempt to add an additional "EDUC" [DISCIPLINE] to the "Human & Public Service" [PATHWAY], by inserting `Discipline@row = "EDUC",`

This results in #INCORRECT ARGUMENT SET error.

`=IF(OR(Discipline@row = "ACCT", Discipline@row = "ACNT", Discipline@row = "BCIS", Discipline@row = "BMGT", Discipline@row = "BUSG", Discipline@row = "BUSI", Discipline@row = "ECON", Discipline@row = "HIST", Discipline@row = "MRKG", Discipline@row = "POFI", Discipline@row = "POFT"), "Business & Industry", IF(Discipline@row = "ARTS", "Arts & Humanities", IF(Discipline@row = "ANTH", Discipline@row = "EDUC", "Human & Public Service", IF(Discipline@row = "CHEM", "STEM", IF(Discipline@row = "N/A - Staff/Admin", "Staff/Admin")))))`

• ✭✭✭✭✭✭
Options

You need to use the same syntax with the OR function that you used in the first set.

• ✭✭✭✭
Options

Gave this a shot and got the same error...

`=IF(OR(Discipline@row = "ACCT", Discipline@row = "ACNT", Discipline@row = "BCIS", Discipline@row = "BMGT", Discipline@row = "BUSG", Discipline@row = "BUSI", Discipline@row = "ECON", Discipline@row = "HIST", Discipline@row = "MRKG", Discipline@row = "POFI", Discipline@row = "POFT"), "Business & Industry", IF(Discipline@row = "ARTS", "Arts & Humanities", IF(OR(Discipline@row = "ANTH", Discipline@row = "EDUC", "Human & Public Service", IF(Discipline@row = "CHEM", "STEM", IF(Discipline@row = "N/A - Staff/Admin", "Staff/Admin")))))`

• ✭✭✭✭
Options

I've gone syntax blind, but apparently my attempt isn't the fix:

`IF(OR(Discipline@row = "ANTH", Discipline@row = "EDUC", "Human & Public Service",`

• ✭✭✭✭✭✭
Options

You are forgetting to close out the OR statements.

=IF(OR(.......), "Business Industry", IF(OR(.......), "Human & Public Service"

• ✭✭✭✭
Options

That seems to have done the trick. Below is the formula that seems to do everything I'm trying to accomplish. Thanks a bunch, Paul!

`=IF(OR(Discipline@row = "ACCT", Discipline@row = "ACNT", Discipline@row = "BCIS", Discipline@row = "BMGT", Discipline@row = "BUSG", Discipline@row = "BUSI", Discipline@row = "ECON", Discipline@row = "HIST", Discipline@row = "MRKG", Discipline@row = "POFI", Discipline@row = "POFT"), "Business & Industry", IF(OR(Discipline@row = "ARTS", Discipline@row = "COMM", Discipline@row = "DANC", Discipline@row = "DRAM", Discipline@row = "ENGL", Discipline@row = "HRPO", Discipline@row = "HUMA", Discipline@row = "MUSI", Discipline@row = "PHIL", Discipline@row = "SPAN", Discipline@row = "SPCH", Discipline@row = "STSC"), "Arts & Humanities", IF(OR(Discipline@row = "ANTH", Discipline@row = "EDUC", Discipline@row = "FREN", Discipline@row = "GEOL", Discipline@row = "GOVT", Discipline@row = "PSYC", Discipline@row = "PSYT", Discipline@row = "SOCI"), "Human & Public Service", IF(OR(Discipline@row = "BIOL", Discipline@row = "CHEM", Discipline@row = "COSC", Discipline@row = "GEOG", Discipline@row = "INRW", Discipline@row = "ITXX", Discipline@row = "KINE", Discipline@row = "MATH"), "STEM", IF(Discipline@row = "N/A - Staff/Admin", "Staff/Admin")))))`

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!