Create drop down then IF statement?

    It sounds simple, but maybe it just isn't. I'm trying to create a database of addresses with a lookup front page. If the user enters a postcode in one box it will give them the possible addresses linked to that postcode in a dropdown. On choosing the required address the three required fields will be automatically populated. The same postcodes will not necessarily mean the 3 data fields will be the same etc, etc. The database may become quite large but there will be nothing more difficult than the above.


    I have attached a simple file to try and demonstrate.
    forum.ozgrid.com/index.php?attachment/46682/


    Thanks

    Re: Create drop down then IF statement?


    First create a named range for your data in the Data sheet... for example select A4:E37 and name that range Data.


    Then in the Front sheet, select D13, go to Data Validation, select List from the Allow menu and enter formula:


    =INDEX(Data,MATCH($B$13,INDEX(Data,0,1),0),2):INDEX(Data,MATCH(2,INDEX(1/(INDEX(Data,0,1)=$B$13),0)),2) in the Source field.


    Now you can select address based on postal codes. *Note: This assumes that postal codes are grouped together in the Data sheet.


    Then in G6:


    =IF($D$13="","",INDEX(Data,MATCH(1,INDEX((INDEX(Data,0,1)=$B$13)*(INDEX(Data,0,2)=$D$13),0),0),3))


    in G10


    =IF($D$13="","",INDEX(Data,MATCH(1,INDEX((INDEX(Data,0,1)=$B$13)*(INDEX(Data,0,2)=$D$13),0),0),4))


    and G15:


    =IF($D$13="","",INDEX(Data,MATCH(1,INDEX((INDEX(Data,0,1)=$B$13)*(INDEX(Data,0,2)=$D$13),0),0),5))

    Re: Create drop down then IF statement?


    This seems to cover what I need, thanks


    I assume that - no matter how large the database is - as long as the named range covers all the data it wouldn't be a problem with the size? (number of rows)

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Create drop down then IF statement?


    Yes, that's right, but it might get slower at processing, since these formulas are really array formulas... so try to keep at minimum necessary

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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

Register Yourself Login