Complex Formula

    I am Looking To creat A Formula that allows me to be able to click on a cell and have a drop down list (Which know how to do) But then have a second cell that if i have Option A from the list in gives me a Certain drop down list, and is i have Option B then it gives me a diffrent drop down list. Example is if I have Monday-Friday as list. I pick on one cell monday it gives me 1-5, And if I pick Friday it gives me 20-25!

    Complex Formula


    I am Looking To creat A Formula that allows me to be able to click on a cell and have a drop down list (Which know how to do) But then have a second cell that if i have Option A from the list in gives me a Certain drop down list, and is i have Option B then it gives me a diffrent drop down list. Example is if I have Monday-Friday as list. I pick on one cell monday it gives me 1-5, And if I pick Friday it gives me 20-25! So if Cells A1-A5 have a list of days as assumed in a valdation eqaution, then picking monday in cell A1,then in cell B1 it will give me a list with 1-5. f A1 then is changed from monday to tuesday,then in cell b1 will give me a list of 6-10, ext....for a1-a5 for b1-b5

    Data Validation List Based on Cell Value


    Hi and welcome to Ozgrid.


    Just a reminder to make your post titles reflective of your problem. This really has more to do with Data Validation List Based on Cell Value.


    There have been many threads on this that a search would bring up; however, one of the best places to start is at:
    http://www.ozgrid.com/News/excel-dependent-lists.htm

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

    Re: Complex Formula


    I see that Brister already posted a solution. From his writeup I think the attached is a bit different than what he described.


    Cell A3 uses Data Validation with an Indirect function to pull in the Named Range than matches the name in cell A1.


    I also included a ChangeDetection macro in the sheet's private module to clear the contents of cell A3 whenever cell A1 is changed.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then Range("A3").ClearContents
End Sub


    EDIT: P.S. Just looked at Brister's examples. I liked the way the CHOOSE formulas were used. (Might get a bit unwieldly if too many options were possible in the first cell, but it's definite more space efficient than my multiple named ranges. But named ranges might be easier to update/maintain.) Thansk for providing that, Brister.

    Re: Complex Formula


    Just notice that this is a Duplicate post. That's a No No, KubiKu. You just had two folks answer a question in the second thread taht was already answered. Please re-read the rules for posting on OzGrid.


    I'm going to try to combine the posts.

    Re: Complex Formula


    Well cool, I learned something new today. I started off that direction, but didn't know you could put a formula within the validation range. Cool Thanks Thomach.

    Re: Complex Formula


    first time i posted, think you could give me a break. Anyway thanks for the quick response to the post. i still can't get it, will have to play a little more with the formulas.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login