Data Validation to pre-populate answer in the next column or return a drop-down

**svetauruss** · 10-10-2015, 08:04 PM

Hi Everyone,

I am trying to create a template where I have a data validation drop-down in column G with a question "Is this a New Vendor? (Y/N)" and a drop-down selection of "Yes" and "No". The next column (Column H) is called "Service Start Month". I would like to add a formula to column H which will depend on the drop-down selection in column G. If someone selects answer "No" in column G, I would like column H to display "FEB" as a result. If someone selects "Yes" in column G, I would like column H to show a drop-down with all 12 months for a person to select one. As of now, I've used an "INDIRECT" function in data validation, so whenever someone selects "No" they are only able to select "FEB" in column H. But, I don't know how to make it to automatically pre-populate so that they didn't have to select "FEB" if they answered "No" in prior column.

I had a feeling that this could have been accomplished by combining VLOOKUP and Data Validation in one cell, but cannot figure out how to achieve it. Also, I think I saw in some other threads that this could be done via macro. If so - is it possible to set this macro to be run automatically whenever someone else is filling this out? It will be used by multiple users. Thanks!

Capture.PNG

**JBeaucaire** · 10-10-2015, 09:12 PM

The trick is to put a FORMULA in H2 before you add the data validation.

=IF(G2="No", "FEB", "")

Let's assume your months are listed somewhere in the workbook in 12 cells you've given a "name" in the Name Wizard as MonthList.

After that formula is in place, add your data validation like so:

=IF(G2="Yes", MonthList)

No messy Indirect need.

So now the drop down in column H won't exist at all unless column G says 'Yes", and if it says "No", then "FEB" will appear as a result of the formula.

NOTE: If someone uses the drop down in H2 to select a month, then the formula in that cell is removed, certainly, so changing G to "No" won't fix that. That's a training issue.

**sandy666** · 10-10-2015, 09:29 PM

I think you can do this with one formula inside DataValidation:

Define NAME NO =$AA$1 where will be Feb
Define NAME YES =$AB$1:$AB$12 where will be list of months
G2: (DV-List) directly Yes,No
H2: (DV-List) =IF(G2="no",NO,YES)

edit:
ups, I forgot about pre-populate Feb, sorry

**svetauruss** · 10-10-2015, 09:31 PM

Thank you so much for such a quick response!! This is exactly what i was looking for

**svetauruss** · 10-10-2015, 09:41 PM

Thank you!

**sandy666** · 10-10-2015, 10:46 PM

[ ...deleted... ]

If Target.Column = "G2" Then

should be

If Target.Column = 7 Then

Data Validation to pre-populate answer in the next column or return a drop-down

LinkBack

Thread Tools

Rate This Thread

Display

Data Validation to pre-populate answer in the next column or return a drop-down

Re: Data Validation to pre-populate answer in the next column or return a drop-down

Re: Data Validation to pre-populate answer in the next column or return a drop-down

Re: Data Validation to pre-populate answer in the next column or return a drop-down

Re: Data Validation to pre-populate answer in the next column or return a drop-down

Re: Data Validation to pre-populate answer in the next column or return a drop-down

Thread Information

Users Browsing this Thread

Similar Threads

Drop Down, Single Answer, Auto Populate

Populate cell data based on data validation (drop down list)

Auto Populate Dependent Drop Down List if there is only one answer

Look-up column via drop-down box then populate userform with row data

Need Help on Data Validation to return either a value or drop down list and other queries

Data Validation-Auto populate from a drop down menu

[SOLVED] How to reference/populate a column of data based on a drop down menu selection

Return Value Next To Column With Data Validation

Bookmarks

Bookmarks

Posting Permissions