1 Reply Latest reply: Jan 30, 2013 1:22 AM by Barry
Ngh07 Level 1 Level 1 (0 points)

I am trying to create an address field that corresponds with a client drop down menu. I want to be able to chose a client and the address automatically pops up?  I think I need to use a combination of an "IF" statement and "lookup" but I'm not sure how. Any help would be great,

 

Thanks


MacBook, Mac OS X (10.6.8)
  • 1. Re: I am trying to create an address field that corresponds with a client drop down menu. I think I need to use a combination of an "IF" statement and "lookup" but I'm not sure how.
    Barry Level 7 Level 7 (29,180 points)

    Hi N',

     

    Place the client information onto a separate table, with all of the information for one client in a single row. For the formula below, I've named that table "Data", and placed the Name in column A and the first line of the Address information in column B. Place the other data into other columns, keeping each column for one type of data.

     

    The names on the pop-up menu must be exact matches for those in the Data table. The Data table may be placed on a separate Sheet from the Main table.

    Picture 25.png

    Main, Column A contains the pop-up menus. The first three are set of names on the menu. The rest are set to the 'empty' menu item. While it appears 'empty', the item actually includes a single spaceas near as we mortals can get to setting a "blank" menu item.

     

    The menu was created in cell A2, then filled down to A10.

     

    B2 contains the Lookup formula:

     

    B2: =LOOKUP($A,Data :: $A,Data :: B)

     

    This can be filled right to return the next piece of data for each name, as seen in column C of Main.

    Filled right to column D, the formula returns data from column D of Data. As I wanted to place data from column H into this column, I replaced the reference to Data :: D in this cell ("Data :: B" in the original, above) with Data :: H.

     

    The three copies of the Lookup formula are also filed down to row 10.

     

    Regards,

    Barry