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

Draykov

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

N/A = ADMIN

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!

https://community.smartsheet.com/discussion/77610/basic-formula-to-auto-populate-one-cell-based-on-selection-in-another-cell

Reference question

Paul Newcome

You are forgetting to close out the OR statements.

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

Paul Newcome

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

Draykov

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")))))

Paul Newcome

What about the broken formula that you had attempted?

Draykov

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")))))

Paul Newcome

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

Draykov

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")))))

Draykov

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

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

Paul Newcome

You are forgetting to close out the OR statements.

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

Draykov

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")))))

Paul Newcome

Happy to help. 👍️