How to Populate a list box using multiple lookup

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to Populate a list box using multiple lookup

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to Populate a list box using multiple lookup

     
    Hello all,

    I tried to use a couple of lookup functions but I can't seem to get excel to except my formulas in data validation.

    Errors is a Drop Down list in which I would like populated with what is selected in the error group List. The data I would like shown in the list is the Error Description from the 2nd sheet I have posted. As you can see there is 8 rows of data in the same group PhysSub:CJD I'd like to display all the errors in the Error drop down.

    Employee ID:304Acevedo, TheresaError Group:PhysSub:CJD
    Date RangeErrors:
    11/1/08to11/30/08


    Grouping

    Error CodeError Description
    DonorFloor:DocumentationA1-1SPE NOT COLLECTED ON DUE DATE.
    DonorFloor:DocumentationA1-2SPE NOT INITIALED AS BEING COLLECTED.
    PhysSub:5203A3-495203 CONSENT FORM MISSING FROM DRF.
    PhysSub:5203A3-685203 CONSENT NOT ENTERED INTO DMS.
    PhysSub:5203A3-25203 PHYSICIAN NOTIFICATION OPTION INCOMPLETE./COMPLETED INCORRECTLY.
    PhysSub:5203A3-60DONORS INITIAL(S) MISSING ON 5203 CONSENT FORM.
    PhysSub:AnthraxA3-66ANTHRAX INFORMED CONSENT NOT INSERTED INTO DMS.
    PhysSub:AnthraxA3-52ANTHRAX CONSENT FORM MISSING FROM DRF.
    PhysSub:AnthraxA3-7ANTHRAX CONSENT FORM NOT ENTERED IN DMS ON DATE CONSENTED.
    PhysSub:CJDA3-5CJD CONSENT NOT INITIATED.
    PhysSub:CJDA3-6CJD DIRECT QUESTIONS NOT COMPLETED.
    PhysSub:CJDA3-4CJD NOT ENTERED INTO DMS.
    PhysSub:CJDA3-70CJD ONE TIME DIRECT QUESTIONS NOT COMPLETE.
    PhysSub:CJDA3-45DATE MISSING ON CJD FORM.
    PhysSub:CJDA3-13DONOR SIGNATURE MISSING ON CJD FORM.
    PhysSub:CJDA3-22INCORRECT DATE(S) LISTED ON CJD FORM.
    PhysSub:CJDA3-42WITNESS SIGNATURE ON CJD FORM MISSING.
    PhysSub:ImmunizationsA3-11DONOR ACCEPTED FOR IMMUNIZATION PROGRAM WITH NO CONSENT.
    PhysSub:ImmunizationsA3-53DONOR CHECKED ACCEPTED BUT NO IMMUNIZATION LISTED.
    PhysSub:ImmunizationsA3-9DONOR NOT ACCEPTED FOR IMM PROGRAM ON PHYS. EXAM FORM.


    Thanks.

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    Hi,


    Excel Jeanie HTML ******>Sheet2

    *ABCDEF
    2***AddressTotal CountStart Row #
    3*PhysSub:CJDCJD NOT ENTERED INTO DMS.C15:C22815
    4******
    5GroupingError CodeError Description***
    6DonorFloor:DocumentationA1-1SPE NOT COLLECTED ON DUE DATE.***
    7DonorFloor:DocumentationA1-2SPE NOT INITIALED AS BEING COLLECTED.***
    8PhysSub:5203A3-495203 CONSENT FORM MISSING FROM DRF.***
    9PhysSub:5203A3-685203 CONSENT NOT ENTERED INTO DMS.***
    10PhysSub:5203A3-25203 PHYSICIAN NOTIFICATION OPTION INCOMPLETE./COMPLETED INCORRECTLY.***
    11PhysSub:5203A3-60DONORS INITIAL(S) MISSING ON 5203 CONSENT FORM.***
    12PhysSub:AnthraxA3-66ANTHRAX INFORMED CONSENT NOT INSERTED INTO DMS.***
    13PhysSub:AnthraxA3-52ANTHRAX CONSENT FORM MISSING FROM DRF.***
    14PhysSub:AnthraxA3-7ANTHRAX CONSENT FORM NOT ENTERED IN DMS ON DATE CONSENTED.***
    15PhysSub:CJDA3-5CJD CONSENT NOT INITIATED.***
    16PhysSub:CJDA3-6CJD DIRECT QUESTIONS NOT COMPLETED.***
    17PhysSub:CJDA3-4CJD NOT ENTERED INTO DMS.***
    18PhysSub:CJDA3-70CJD ONE TIME DIRECT QUESTIONS NOT COMPLETE.***
    19PhysSub:CJDA3-45DATE MISSING ON CJD FORM.***
    20PhysSub:CJDA3-13DONOR SIGNATURE MISSING ON CJD FORM.***
    21PhysSub:CJDA3-22INCORRECT DATE(S) LISTED ON CJD FORM.***
    22PhysSub:CJDA3-42WITNESS SIGNATURE ON CJD FORM MISSING.***
    23PhysSub:ImmunizationsA3-11DONOR ACCEPTED FOR IMMUNIZATION PROGRAM WITH NO CONSENT.***
    24PhysSub:ImmunizationsA3-53DONOR CHECKED ACCEPTED BUT NO IMMUNIZATION LISTED.***
    25PhysSub:ImmunizationsA3-9DONOR NOT ACCEPTED FOR IMM PROGRAM ON PHYS. EXAM FORM.***

    Spreadsheet Formulas
    CellFormula
    D3=ADDRESS(F3,3,4)&":"&ADDRESS(F3+E3-1,3,4)
    E3=COUNTIF(A6:A65536,B3)
    F3=MATCH(B3,A:A,0)


    Excel tables to the web >> Excel Jeanie HTML 4






    Select C3, go to data validation > List > source: =INDIRECT(D3)

    HTH

  3. #3
    New Member
    Join Date
    Nov 2008
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    Hi Kris,

    Thanks so much for your response! The validation will not allow a reference to another worksheet. So the only way I see is to have everything on one page which I didn't want to do.

    Is there anyway to allow a reference to another sheet?

    I'm using Excel 2003 WinXP

    Thanks Again!
    djcali

  4. #4
    Board Regular chrispy's Avatar
    Join Date
    Apr 2008
    Location
    florida
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    Create links to relevent data on current sheet, then hide those rows.

  5. #5

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    Hi djcali,

    Assume that your data on Sheet1

    Validation on Sheet2

    Define

    Count

    Refers to: =COUNTIF(Sheet1!$A$6:$A$65536,Sheet2!$B$3)

    where sheet2b3 houses grouping validation cell

    Define StartRow

    Refers to: =MATCH(Sheet2!$B$3,Sheet1!$A:$A,0)

    Define List

    Refers to: ="Sheet1!"&ADDRESS(StartRow,3,4)&":"&ADDRESS(StartRow+Count-1,3,4)

    Now goto validation, allow List, Source: =INDIRECT(List)

    HTH

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,302
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    If you use named ranges, Validation will work across worksheets.
    To adapt it to Krishnakumar's solution in post #2, defining a name

    Name: namedRange
    RefersTo = INDIRECT(Sheet1!$D$3)

    will allow you to use =namedRange as a validation list source on any sheet.


    (Edit: Krishnakumar's post above is another way to use names to get around that restriction)
    Last edited by mikerickson; Nov 18th, 2008 at 10:59 AM.

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

    Kris,

    Worked like magic!

    Thanks so much for your time!

    djcali

    Thank you mikerickson!

  8. #8

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Populate a list box using multiple lookup

      
    Quote Originally Posted by djcali View Post
    Kris,

    Worked like magic!

    Thanks so much for your time!

    djcali
    You are welcome!!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com