Can you have a column formula in a column that has a drop down list?

KHoward

Hi Everyone,

My dropdown list is not giving me the option to make a selection now that I have a If formula in it. 

Column 1 = Team (this is a drop down list with the following teams: Career Development, Instructional Design, Learning Solutions, Leadership Development, Talent and Learning Ops & Infrastructure)

Column 2 = LDO Objectives (The formula in this column is =IF(Team@row = "Talent and Learning Ops & Infrastructure", "N/A")

What I am trying to achieve is there is one team in column 1 that does not need to complete column 2, so I want column 2 to be N/A when that team is selected. However when other teams are selected, they still need  the ability to make a selection from the drop down, but the drop down option is not there once I had the formula.

Can you have a column formula in a column that is a drop down? If so what is the fix?

Philip Robbins

Hi @KHoward,

Column formulas totally override any ability to select or enter data within the cells of that column. You would need a separate helper column with the dropdown that the other teams can select from, then in your LDO Objectives column pull the value from that new column or display N/A otherwise.

If you are expecting people to enter the information via a form, you could get rid of the formula and set the LDO Objectives value to N/A by default. Then you could use logic to present the LDO Objectives field when any of the teams requiring it are selected. Just a thought.

One final option, which I wouldn't advise but I'm sharing for info, is to convert your formula to a cell formula. That way it can be overridden by a selection or manual entry. Any entry will wipe out the formula for that cell, so you would have to manually reinstate it in the event of an error, so it's not a robust solution. I use column formulas wherever possible and helper columns to get around issues like the one you're facing.

Hope this helps.

KHoward

Thanks @Philip Robbins this has been helpful, a few clarifications if you don't mind as I have not applied your suggestions yet.

Here is the formula I am using in the LDO Objectives column: =IF([LDO Team]@row = "Talent and Learning Ops & Infrastructure", "N/A")

For option 1, is the Helper column another Column 1, the team column? Meaning I would have two Teams columns to pull from? Or is the helper column another LDO Objectives column for the Career Development, Instructional Design, Learning Solutions, Leadership Development to pull from.

on your option 2, the teams can enter the information via a form or a sheet. This is because the sheet gets updated regularly, and sometimes when they have multiple entries and it's easier to do it via the sheet rather than a form. So can I use the default in a form and still have the formula in that column?

Option 3 is tempting. :)

Philip Robbins

Hi @KHoward,

Column formulas totally override any ability to select or enter data within the cells of that column. You would need a separate helper column with the dropdown that the other teams can select from, then in your LDO Objectives column pull the value from that new column or display N/A otherwise.

If you are expecting people to enter the information via a form, you could get rid of the formula and set the LDO Objectives value to N/A by default. Then you could use logic to present the LDO Objectives field when any of the teams requiring it are selected. Just a thought.

One final option, which I wouldn't advise but I'm sharing for info, is to convert your formula to a cell formula. That way it can be overridden by a selection or manual entry. Any entry will wipe out the formula for that cell, so you would have to manually reinstate it in the event of an error, so it's not a robust solution. I use column formulas wherever possible and helper columns to get around issues like the one you're facing.

Hope this helps.

KHoward

Thanks @Philip Robbins this has been helpful, a few clarifications if you don't mind as I have not applied your suggestions yet.

Here is the formula I am using in the LDO Objectives column: =IF([LDO Team]@row = "Talent and Learning Ops & Infrastructure", "N/A")

For option 1, is the Helper column another Column 1, the team column? Meaning I would have two Teams columns to pull from? Or is the helper column another LDO Objectives column for the Career Development, Instructional Design, Learning Solutions, Leadership Development to pull from.

on your option 2, the teams can enter the information via a form or a sheet. This is because the sheet gets updated regularly, and sometimes when they have multiple entries and it's easier to do it via the sheet rather than a form. So can I use the default in a form and still have the formula in that column?

Option 3 is tempting. :)