Excel if statement for dropdown list

dear experts,

i have a dropdown list with different email suffixes like in cell B15
gul.com
gal.com
test.com
gil.com
dal.com

in cell B20 i have also a dropdown with changes different parameters depends on the values i can choose there GUL,GAL,TEST,GIL,DAL. Now i like to include a if statement in the suffixlist above depends on the value in C20
which should automatically change to the correct email suffix in B15.

Thanks in advance
LVL 2
Mandy_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Once you put a formula in B15, then the drop down becomes "unnecessary" because it is your formula and not the dropdown which will determine the value. Correct?

That said, you can just put this formular in B15
=IF(ISBLANK(C20), "", LOWER(C20) & ".com")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mandy_Author Commented:
hi,

thanks for your comment. I've 6 different values in C20 it stands for different companies. Every company getting
his own email domains as suffix. If i change the value in this dropdown the smtp will automatically build
for the selected value / company.

Generally every value i choose has a fixed smtp. These 6 fixed prefixes (test.com,....) in the dropdown should
change automatically with the selected value in C20. Unfortunately some times there are exceptions.
These exceptions (10) are also values of the dropdown in B15 but should select manually.
I dont like to have 16 different values in C20 because the 10 values not needed very often but if one
of them needed i will select manually.


Thanks in advance
mandy

i could use your formula instead "isblank" insert the selected value of c20?
0
MlandaTCommented:
Dealing with those exceptions is the tricky bit. An alternative is to perhaps use three celles instead of two.

C20: use as is
B14: if user has selected one of the exceptions, then they must select/type a value here. You can use conditional formatting to set the textcolor in this cell to be the same as background colour of the cell if the user is not required to type in anything in there AND highlight the cell if they should do somethung in there (just a visual cue)
B15: the final cell with a formula. If C20 is one of the ordinary scenarios, then just use formula above which just adds ".com" to C20. If it is one of the exceptions, then B14 will be highlighted and user has to select value or type in.

I hope that makes sense. Its not possible to have one cell in which the user can either type in without overriding the formula
0
Mandy_Author Commented:
Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.