Data Validation Listing Based on Certain Condition

Dear Gurus,

Can we have Data validation list based on certain criteria. For example in data validation drop down, when I select 'Apr' it should list down only April month related weeks in another data validation column.

Sample data provided below:
Data validation list maintained in separate sheet
02-Apr-15 To 08-Apr-15 Apr
09-Apr-15 To 15-Apr-15 Apr
16-Apr-15 To 22-Apr-15 Apr
23-Apr-15 To 29-Apr-15 Apr
30-Apr-15 To 06-May-15 Apr
07-May-15 To 13-May-15 May
14-May-15 To 20-May-15 May
21-May-15 To 27-May-15 May
28-May-15 To 03-Jun-15 May
04-Jun-15 To 10-Jun-15 Jun
11-Jun-15 To 17-Jun-15 Jun
18-Jun-15 To 24-Jun-15 Jun
25-Jun-15 To 01-Jul-15 Jun
02-Jul-15 To 08-Jul-15 Jul
09-Jul-15 To 15-Jul-15 Jul
16-Jul-15 To 22-Jul-15 Jul
23-Jul-15 To 29-Jul-15 Jul
30-Jul-15 To 05-Aug-15 Jul
06-Aug-15 To 12-Aug-15 Aug
13-Aug-15 To 19-Aug-15 Aug
20-Aug-15 To 26-Aug-15 Aug
27-Aug-15 To 02-Sep-15 Aug

In the new sheet there should be two columns, Week & Month with data validation. When I select Apr from the drop down, the week column should list down only April month related weeks.

Please advise how to achieve this...

Attaching the sample file for your reference...

Originally Posted by FDibbins

I would suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi ChemistB,

Its working perfectly for only one row. In your sample file also you have tested with data validation for only one row.

How to do it data validation with multiple rows? for example, the first row column A, I'll select Apr and the second row column A, I'll select May.

Regards,
Suresh

Originally Posted by ChemistB

Assuming the answer to my last question was yes, I did it this way
In Weeks! col D, I changed the formula to
In D2 copied down
=TEXT(A2, "mmm")

In E2 (you can hide this column later)
=IF(D2=Data!$A$2,D2&COUNTIF($D$1:$D2,D2),"")
this creates a unique list of April (or whatever month is chosen in Data!

In G2 copied down to G19 (because I figured that was far enough)

=IFERROR(INDEX($C$2:$C$27, MATCH(Data!$A$2&ROWS($G$2:$G2), Weeks!$E$2:$E$27,0)),"")
that pulls the appropriate month over to a congruous list

I created 2 defined names (in Formula Tab of ribbon)
Lastrow =MAX((LEN(Weeks!$G$2:$G$19)>0) * ROW(Weeks!F2:F19))-1

and
List =Weeks!$G$2:INDEX(Weeks!$G$2:$G$19, Lastrow)

Then for your data validation in Data!B2
=List

Hi John,
Still it is working for only one line item... Please check the attached file, data sheet...

You don't need a formula in G2: the macro just inserts the chosen month for use by the other formulas provided by ChemistB.

Hello,
The sheet I am currently working in has been setup for techs to enter field results and lab testing results with conditional formatting used to colour code results when outside a few parameter sets to quickly alert possible concern areas for managers to view easily.All the formatting works great except I am stuck on the formula to meet the last parameters and need help. All CF is determined from what is entered into the cell based on data from other cells. However the last one is based on Column A that contains alpha numeric batch codes. The Cell in Column P needs to be formatted Red based on the number entered being less than the target in Column N only when either codes 30C20IW or 30D10DW appear in Column A (from a massive drop down list referenced on another page).
What is the formula to combine the different conditions; so the new format based on these new requirements applied only when the two specic codes appear?

John,

I don't see any macros in your sheet...

Originally Posted by JohnTopley

You don't need a formula in G2: the macro just inserts the chosen month for use by the other formulas provided by ChemistB.

The macro is the Sheet "Data" : Right click on tab and "View Code"

@CWALES: please start your own thread.

Dave,
When I tested, the last week of previous month appeared in the dropdown.

John

Sorry I didn't explain my query very well: If you select May you get the drop down below:

30-Apr-16 06-May-16 30-Apr-16 To 06-May-16 Apr
07-May-16 13-May-16 07-May-16 To 13-May-16 May
14-May-16 20-May-16 14-May-16 To 20-May-16 May
21-May-16 27-May-16 21-May-16 To 27-May-16 May
28-May-16 03-Jun-16 28-May-16 To 03-Jun-16 May

my understanding is the dropdown should start with 07-May entry not the last entry of the previous month.

John

Re "Clearing" the dropdown on new selection: Like you I (vaguely) recall seeing answer - I'll have a trawl and see if I can find something.

One option ...

http://blog.contextures.com/archives...op-down-cells/

... see what the OP says!

Thanks a Lot FlameRetired... It worked like a Charm...

Originally Posted by FlameRetired

Another formula approach.

In Weeks sheet month headers are in E1:P1.

Then this array-entered formula in E2 filled down and across to P9 or until you get all blanks under each month.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then redefine the Data validation formula for B2 in the Data sheet as

Formula:

Please Login or Register  to view this content.

It is important to make sure the $A2 reference in the formula is set relative to row.

Then redefine Data validation for A2 to

Formula:

Please Login or Register  to view this content.

Fill the drop downs in A2 and B2 down as needed.