Data validation with drop down formulas

**wtucker** · 05-25-2017, 03:00 PM

Hey there everyone,

This is my first post. I'm here after an hour of fruitless online research. I'm hoping someone out there can help!

I have two columns. Each one contains a date. The first one is manually entered, and I would like the second to have data validation, with 2 drop-down options. However, the options need to be based on the date in Column1. I want one to be 6 months out, and the other 12 months out.

For example:
Column1 Column2
1/1/2017 either 7/1/2017 or 1/1/2018
2/1/2017 either 8/1/2017 or 2/1/2018

So far I haven't had any luck creating a list with the edate formula, and I don't know how I could create these options with a table on another tab, as the date is based on a call reference. Any help would be greatly appreciated.

- Will

**Tsjallie** · 05-25-2017, 04:27 PM

See if you can do something with this idea.

**alansidman** · 05-25-2017, 04:31 PM

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

**wtucker** · 05-25-2017, 04:39 PM

Hey Tsjallie,

Thanks for the reply! I can set it up with a data validation reference for each row to match options in a corresponding row on another tab, which are themselves edate formulas based on the dates in column1. It'll take some time, as I need it for about 100 rows, but I think that's probably better than continuing my search for an easier way.

Thank you!

**Tsjallie** · 05-25-2017, 04:54 PM

@ Alan, you sure got a point there. I'll get my coat

@Will,
I'm posting a workbook with a better solution, but with a small piece of vba in it.
The vba catches the event when a cell is selected. If that is in column B than the date in column A of the same row will be copied to the first cell of the validation list.

Please Login or Register  to view this content.

**FDibbins** · 05-26-2017, 02:24 AM

How about if you add a helper column (C?)and use that for a "switch" to determine if you use 6 or 12 months, this could be a DD that says 6 or 12?
Then, based on that, you have a simple formula in B that, based on what is selected in C, adds either 6 or 12 mths to A?

Data validation with drop down formulas

LinkBack

Thread Tools

Rate This Thread

Display

Data validation with drop down formulas

Re: Data validation with drop down formulas

Re: Data validation with drop down formulas

Re: Data validation with drop down formulas

Re: Data validation with drop down formulas

Re: Data validation with drop down formulas

Thread Information

Users Browsing this Thread

Similar Threads

Data validation drop down with ghost drop down

data validation drop down menu only allowed if previous drop down cell is completed

Drop down data validation

Data validation: Can I use data validation in a cell that has formulas in it?

Data validation in drop down box

Drop down data validation

[SOLVED] Data Validation - Drop Down

Bookmarks

Bookmarks

Posting Permissions