IF condition in Data Validation

[e4excel]

Is it possible to combine an IF condition in Data Validation?

Dear Forum,

I am trying to combine the Functionality of a List in Data validation and an Independent Single value if a particular condition's not met..

It goes like this...?

I have three Options of Investment :
1. Option_I
2. Option_II
3. Option_III


I want 2 conditions working based on the three selections, like if the USER selects the Option_I then it should display "Actual Dates" and for anything other than "Option_I" it should provide a list as below:

1. 12 MOnths
2. 13 MOnths
3. 14 MOnths
4. 15 MOnths
5. 16 MOnths
6. 17 MOnths
7. 18 MOnths. --This LIst is Name Defined as AdvancePlanning

Actually, I tried using the formula but in vain:

Please Login or Register  to view this content.

It does not work...

Am I making a mistake somewhere? or its not possible?

Warm Regards
e4excel

[JBeaucaire]

AdvancedPlanning is a named range with the dates in them, yes?

Put the words "Fixed Dates" in a cell, then name that cell something like Fixed.

Then change your formula to:

=IF($B$12="Option_I", Fixed, AdvancePlanning)

[e4excel]

Dear Jb,

So, eventually, the Data validation has to contain only Name Defines , Is it?
I got your point but actually was not thinking of allocating a cell for just displaying a sentence..


The other things is I wanted the result change in the cell to the displayed message back & forth on changing the options!

Ex:

=IF($B$12="Option_I", Fixed, AdvancePlanning)

If the Name Fixed contains "Fixed Dates" then on changing the OPtion to OPtion_I it should that and and any other OPtion other than I should show the first option in the list i.e. 12 MOnths

REgards
e

[JBeaucaire]

That's not going to happen with Data Validation. All that will give you is the options you have to choose from, even if there's only one option for OPTION_1.

You could use a formula to do this, but as soon as someone uses the DV drop down to select something OTHER than the first option, the formula would be removed, and you would be back to manual only updates.

You could add VBA (macros) to your workbook to help, but that's a whole new level of plumbing and macros will have to be enabled all the time for that.

[e4excel]

The query is as good as SOLVED however just asking purely for Academic Interest, in using an IF condition the Data Validation FUnctionality Supercedes the IF CONDITION?

[JBeaucaire]

No, I was saying you could put a formula in a cell to cause initial values to appear in the cell using very similar methods as your DV functionality, but it's still a formula IN the cell.

Now, if you put DV on top of the cell, too, then the user could click on the cell and see the resulting list of offered options, but if they actually do that and make a choice of any kind from the drop down, their choice would replace the formula. The formula would now be gone and the "initial value" functionality would be gone for good.

[e4excel]

No, I was saying you could put a formula in a cell to cause initial values to appear in the cell using very similar methods as your DV functionality, but it's still a formula IN the cell.

I am sorry but I didnt quite follow the suggestion can you please explain and also if possible work it in the Actual File..

AS I am attaching a file for reference..

REgards
e

[JBeaucaire]

You aren't puttling the DV list and the option formula in the same cell, so this isn't a problem after all.

I removed the defined name AdvancePlan and put the formula into B4 as a standard DV formula. Less maintenance. I also removed the merged cells, unnecessary and create problems for yourself down the road.

I added a macro into the Sheet1 sheet module that watches all the changes you make. If you ever change cell A2, it will put the first value from the resulting DV list in cell B4. The code:

Please Login or Register  to view this content.

[e4excel]

You aren't puttling the DV list and the option formula in the same cell, so this isn't a problem after all.

I removed the defined name AdvancePlan and put the formula into B4 as a standard DV formula. Less maintenance. I also removed the merged cells, unnecessary and create problems for yourself down the road.

Please Login or Register  to view this content.

I just wanted to ask why is there 2 I's in IIF? in the code?

That's very cool , exactly what I wanted but didnt know how to get the same as VBA is not my cup of tea..

This is not possible by just using formulas, iS it?

And one more thing I was trying to put just the formula earlier like what you did but it was giving me Error Prompts strangely so I used a Defined name instead..

Nevertheless, I am happy that I got a desired solution when I thought we had hit a WALL..

Thanks a LOT JB
Warm Regards
e4excel

[e4excel]

Dear JB,

This works well and exactly what I wanted but I just realised that If I move the Lists in another Sheet it fails..

and I get the Error Marked in the below area..

Range("B4").Value = IIf([A2] = "Option_I", _
Range("Fixed").Cells(1), Range("AdvancePlanning").Cells(1))

In my actual file the Ranges to be diplayed are in another sheet and referenced by using name defines, however I dont know whether thats possible in VBA as I tried using your code it does not work and goes in the Debug Mode and just to confirm my doubts I tried Moving t he List in the Column H in your FIle from Sheet 1 to Sheet 2 and the same error occurs confirming my doubts..

So, please explain what needs to be done and in my actual file the Cell where the DV occurs in merged with cell D14 so its one cell C14+D14 merged into one..
Would there be a problem using your superb functionality..!

Less maintenance. I also removed the merged cells, unnecessary and create problems for yourself down the road.

Warm Regards
e4excel

[JBeaucaire]

You can move the named ranges to other sheets in the same workbook and as long as triple verify the named ranges are properly defined, the code should work fine. It won't work with named ranges in other workbooks.

Always include you problematic workbook with posts for fastest proper feedback.

[e4excel]

Dear JB,

I tried doing it in the same File which you have made and after chagning the Location of the List from Sheet 1 to Sheet 2 it stopped Working..

Please find the attached file ..

Warm Regards
e4excel

[JBeaucaire]

I didn't realize the sheet reference was going to be needed in this case... good to know.

Please Login or Register  to view this content.

[e4excel]

Thanks JB for the help, however the Name Define Reference does not cause any issues when using them in formulas but in VBA I suppose its not the same , one needs to explicitly mention the Address, Is it?

Just asking for Academic Interest:
So, if again the reference of the List is changed then it will again throw errors, not that I am going to do that, just for understanding..
Now if I was using the concept of Dependent Validation as in the List changes for an entire column:
Ex:
COL A -- Countries - Again a Drop-Down List - USA,USSR,INDIA,JAPAN,CHINA,UK,AUSTRALIA
COL B -- States - Dropdown List Named for each Country like India - will contain all Indain States such as DELHI,PUNJAB.MAHARASHTRA,GUJARAT , ETC...

So I select IN col a CELL A2 = INDIA THEN IN COl B should immediately display the first value in the India's LISt..though by default it may have USA list..

In your code, its 2 cells getting affected but how do I make this USABILITY across an entire column..

Thanks once again...

[JBeaucaire]

As you add complication to your options, it's best to add simplification to the maintenance of it all.

I start by creating a "lookup table" on the Lists page that provides a SIMPLE way to associate the choices you might make in one cell with a one-word "list name" to be used in another cell. See columns A:B for what I mean. United Kingdom = UK.

Now layout all your various country lists and define the one-word name range for each.

At this point, we can simplify both the DV formula being used in column B on Sheet1 to:

=INDIRECT(VLOOKUP($A4,CountryLists,2,0))

And the code active on that sheet is now able to lookup the correct "list" using the same technique to enter the new starting value in B based on the choice in A.

Please Login or Register  to view this content.

This should give you enough to go on.

[e4excel]

Hey thanks a lot JB,

Actually the first query was my actual requirement however I had used the Dependent Validation in the past but when with your solution I thought of incorporating the UPdate Functionality in the earlier files..

Thanks a ^ Zillion for the help..!

Warm Regards
e4excel