Dependent Data Validation Special Case

Originally Posted by NBVC

If your list of Start Dates to use in Data Validation is in say I1:I7, and you have your Start Date data validation in A1, then go to Data|Validation, select List and enter =$I$1:$I$7

then go to Insert|Name|Define and enter name: List2

In the Refers to field enter formula: =INDEX($I$1:$I$7,$A$1):$I$7

click Ok

Now in B2, activate Data Validation and choose list, and enter formula =List2.

adjust ranges/references to suit.

I am not sure what you want to do with the Products list, you only want to show a list from Pencils downward if 2 is chosen in A1?

Okay may be I was not able to make you understand the requirement check the attachments for reference, I have "Start Period" which is Quarter Based (FY09Q4,FY10Q1,FY10Q2,FY10Q4,FY11Q1) same is with the "End Period" but the End period cannot be less than Start Period like If Start is FY10Q1 end period cannot be FY09Q4. Now depending on what has been selected in Start Date the user should have either FY09 Initiatives or FY10 Initiatives.

Hope this makes the picture clear.

Thanks,
Nukecity83

Originally Posted by NBVC

See attached...

For Start Date, I defined a dynamic range called List1 as:

Please Login or Register  to view this content.

For End Date, I defined a dynamic range dependent on List1 called List2 as:

Please Login or Register  to view this content.

for getting the Initiatives I used formula in E5, copied down:

Please Login or Register  to view this content.

but am not 100% sure that is what you wanted?

Hope this helps.

Okay could you check the attached excel sheet where I have done my part of List Validation and need Help with End Period should be '=' or '>' Start Period.

Thanks,
Nukecity83

Originally Posted by NBVC

Is attached what you need?

Yes, The attached is what exactly I am looking for but I am not able to implement the Validation on End Period so that End Period is always equal or greater than Start Period. Could you help me on that?

Thanks,

Originally Posted by nukecity83

Yes, The attached is what exactly I am looking for but I am not able to implement the Validation on End Period so that End Period is always equal or greater than Start Period. Could you help me on that?

Thanks,

Sorry for the previous response.. I saw the file you provided however the End Period needs to be fixed a little like If start period is FY10Q1 then End period should be showing:
Start Period>>> End Period
FY10Q1>>>>>> FY10Q1
>>>>>>>>>>>>FY10Q2
>>>>>>>>>>>>FY10Q3
>>>>>>>>>>>>FY10Q4
>>>>>>>>>>>>FY11Q1

Could you implement this?

Thanks,

Originally Posted by NBVC

See attached.

Cool Thanks, I have one more enhancement to the same file:

Suppose you have selected the 'Start Period' then 'End period' and finally the 'Initiatives'. Now I want a Macro (if there is any other way apart from Macro that would also be fine) such that if user go backs and change the Start Period to some other value the End Period and Initiatives cells should get cleared so that user can reselect them.

Thanks.

Originally Posted by NBVC

I just saw a similar request this morning here:

http://www.excelforum.com/excel-gene...t-problem.html

see if you can adapt it to your sheet.

Absolutely Brilliant, Thanks a ton.