+ Reply to Thread
Results 1 to 4 of 4

Data Validation with Lookup

  1. #1
    Registered User
    Join Date
    10-22-2012
    Location
    Rexburg, Idaho
    MS-Off Ver
    Excel 2010, Excel 2011
    Posts
    2

    Data Validation with Lookup

    I have the following formula which checks cell B7 for a name and then based on the name allows different countries to be put in cell D7. The conditional formatting dialog box accepts the formula, but all inputs come up as invalid and not allowed... What do I need to tweak?

    =IF(B2=Lookup!$G$1,BA_Countries,IF(B2=Lookup!$H$1,CM_Countries,IF(B2=Lookup!$I$1,MR_Countries,IF(B2=Lookup!$J$1,SR_Countries,
    IF(B2=Lookup!$K$1,TE_Countries,)))))

    Also, how do I make it so that if B7 is blank, a list named All_Countries is the data allowed.

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Data Validation with Lookup

    ok, can you please upload an example? Sounds like you need a dynamic dropdown menu

    if not, you can follow the procedure here:

    http://www.excelforum.com/excel-tips...71#post2849771

    --edit

    The attachment found at that link will be helpful, except for that last condition (if B7 is blank, a list names All_Countries is the data allowed).

    But i have an idea for that part, but need an example from you to work with.
    Last edited by GeneralDisarray; 06-19-2013 at 10:27 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-22-2012
    Location
    Rexburg, Idaho
    MS-Off Ver
    Excel 2010, Excel 2011
    Posts
    2

    Re: Data Validation with Lookup

    Dummy Data.xlsx

    There is some sample data in the exact same format with the same named ranges. I read the post you refered to and it looks like that may be what I'm looking for, I'm concerned however since I've had issues in the past where using Index/Match functions together on large amounts of data really seems to bog down the spreadsheet.

    Ideally with the spreadsheet I could enter an appropriate value into either Salesman or Coutnry columns and have data validation on the empty cell and the Customer column as well, i.e. a Salesman is entered and only his countries and customers are allowed in their respective cell, or, if a country is entered, only that countries salesman is available, along with his countries.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Data Validation with Lookup

    I got a bit wild with this one, see the attachment.

    Looks like it is working like you wanted, but you'll need to adjust your sheet just a bit.

    *Instead of a list of Names with Countries below, I made a table with Country on the left and Names across the top - the table is on your "Lookup" sheet.
    Basically, a 1 tells you the person in that column works in the country for that row. A 0 means that person (top of the column) doesn't work in that country (leftmost side of the row).

    *That table is Named (in the name manager) "fullTable" and is defined as: =Lookup!$A$1:INDEX(Lookup!$1:$1048576,COUNTA(Lookup!$A:$A)+1,COUNTA(Lookup!$1:$1)+1)

    *There are two other named ranges needed called "allCountries" and "allPeople" defined as =Lookup!$A$2:INDEX(Lookup!$A:$A,COUNTA(Lookup!$A:$A)+1) and =Lookup!$B$1:INDEX(Lookup!$1:$1,COUNTA(Lookup!$1:$1)+1)

    NOW the trick was to get the data validation to update with the change of a cell... Actually I was able to make it happen w/o macros but only for one row. I assume you will be using this for many, many rows. So, to get it to work, i needed the following:

    You'll need to make this work for your real worksheet, but the attached example should show you what to do.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1