• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining If and Data Validation

E

elmerg

New Member
Hi all,

I have posted a question here before, and your help worked out immensely. I am at another excel conundrum. Using data validation, I've created a number of lists. However, I need to do a bit more with a drop down list.

Particularly, I have figured out how to make dependent lists using indirect, but that doesn't seem to do what exactly I need. What I need now seems to be to figure out how to combine an if statement and a dropdown. What I need, specifically, is...

A particular cell, let's say A1, says Yes or No via a validation drop down.
If A1 says Yes, it automatically fills in a specific value in another cell, let's say B1.
If it says No, it puts a drop down menu in B2.

I can't for the life of me figure out how. Any help would be appreciated!
 
So when you say put's a drop-down menu, you mean a drop-down control (form control or active-x control)? Or just a data validation drop down?
 
Hi, elmerg!
The example has 2 drop down lists at G1 (country) and at J1 (city). The list of countries is in column A (3 entries) and the list of cities per country is in the 3 columns B: D.
Whenever you select an item in G1 (a country from column A) the list at J1 is filled with the related list from columns B: D. Just opened again and it works. Could you check if you proceeded as described?
And if it works, is that suitable for you?
Regards!
 
I went to redownload it again just to be sure; when I choose something in G1, nothing fills in in J1. I even double checked that the VBA code was there using ALT+F11. If it was working, I could use it to do what I need without much issue at all, though. Macros are enabled (it asks me to both enable macros and enable editing when I open the workbook).

I'm using Excel 2010 myself, as a note.
 
Hi, elmerg!
Have you enabled macros? It was developed in 2010 and I've opened and tested it in 2010 when I posted earlier.
Regards!
 
Yes; I was editing my original post to note that when I got the alert here. The code shown in the workbook is as follows:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$1" Then Range("J1").Value = ""
End Sub

I'm not sure if this will do what I need to do, though, as I need it to have a dropdown list only if one specific entry is in one other cell; if there is any other entry, it automatically fills in from a named range reference in indirect.

I am using indirect to combine the entry of two cells to produce the automatic entry that I need. IE: The formula for the cell without the dropdown in it I'm using is

Code: 
indirect(Clan&Disc1)

which grabs that combined set of words from those named cells to form the third named range.

Essentially, I need it to be...

Code: 
If Clan=Caitiff, Use Dropdown
otherwise
If Clan=Any Other, Use Clan&Disc1
 
Hi, elmerg!
I didn't understand you, but as a blind shot check the uploaded file. If that's not what you're after then consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you.
Regards!
 

Attachments

  • Combining If and Data Validation (for elmerg at chandoo.org).xlsx
    9.3 KB · Views: 15
I'll try. I'm not sure how to explain it any easier than...

Cell A1 displays a word.
Cell A2 either gives a drop down or a single entry based on the word in A1. if A1 has Word Y, it displays a drop down; any other entry in A1 causes A2 to display a single word based on an indirect().

Anyway the manual version is attached.
 

Attachments

  • Manual Combining Drop Down and If.xlsx
    9.6 KB · Views: 14
Hi, elmerg!
Assuming that when you say B2 is B1, what you want to do in B5:B7 can't be done with formulas only. With formulas only you can assign as in my previous example different lists to a cell drop down validation, or a fixed value; but not both. If so, you'll require VBA code, but then we'll be in the 1st scenario where the file at my posted link doesn't run in you machine while in mine does (as well as in others).
I recommend you to download and test that file in other computers, if you have any other available there, or ask a friend to test it for you. It actually works.
Regards!

EDITED

PS: Check this updated version of the last file. Maybe that's what you want.
 

Attachments

  • Combining If and Data Validation (for elmerg at chandoo.org).xlsx
    9.2 KB · Views: 37
Hey SirJB7,

Your most recent version of this will work with a small tweak. I can work with that, and honestly it was a way to do it that my very tired brain hadn't looked at.

Thanks a lot!
 
Hi, elmerg!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top