[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel if statement for dropdown list

Posted on 2014-08-29
4
Medium Priority
?
764 Views
Last Modified: 2014-08-30
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
0
Comment
Question by:Mandy_
  • 2
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
MlandaT earned 2000 total points
ID: 40294519
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40294545
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
 
LVL 31

Assisted Solution

by:MlandaT
MlandaT earned 2000 total points
ID: 40294658
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
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40294756
Thanks for your help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question