Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset formula to choose table data (Excel 2003/SP2)

    I have a list of course categorires where each category has 1 to 30+ courses. I'd created a drop down list for the categories but now need a listbox (from Forms) to auto-populate with the courses for the chosen category. I have a sorted table with the data, but am stuck on the correct structure for using the OFFSET formula.
    <pre>Category_A Course_A1
    Course_A2
    Course_A3
    Category_B Course_B1
    Course_B1
    Course_B2
    Course_B3
    </pre>


    I tried a match to grab the row where the selected course category starts and then wanted to use OFFSET to grab the courses. Am I going in the right direction?

    Deb
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula to choose table data (Excel 2003/SP2)

    Select Insert | Name | Define...
    Create a name called Source with definition

    =OFFSET(Courses!$B$12,MATCH(Courses!$A$4,Designati ons,0),0,OFFSET(Courses!$C$12,MATCH(Courses!$A$4,D esignations,0),0),1)

    Right-click your combo box and set its list fill range to =Source.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula to choose table data (Excel 200

    This is perfect! I tested it and for some reason 3 of the categories don't display anything. I thought there were invalid characters so I rewrote those 3 categories. I marked the 3 in the attached .xls file (see bottom of the page with an 'x' next to them). I even changed them to generic names like aa, bb, cc but those didn't work. It's as if there isn't a match in the list.

    Deb
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula to choose table data (Excel 200

    It's because you haven't placed the formula with the # of Classes for some categories in the same row as the category name, as you did for the others. The defined name expects the # of Classes to be in the same row where it found the category.

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula to choose table data (Excel 200

    Ahhhh, I didn't even notice that (obviously). I added the counts and it works ... of course.

    Thanks, Deb <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Posting Permissions

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