Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List for three cells (2003 )

    Hello Everyone,

    I need some help. I have a spreadsheet that I attached where I want the user to select a list based on the data in sheet2. I am able to do a list for the first two (Building/Facility) but am having trouble on how to proceed to the last one. Any ideas would be great.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List for three cells (2003 )

    How about
    <pre>=INDEX(Data!$C$7:$F$20, MATCH($A$6&$B$6,Data!$C$7:$C$20,0), MATCH($C$6,Data!$C$7:$F$7,0))</pre>

    See attached, your workbook with the formula & spelling error fixed! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List for three cells (2003 )

    Thanks. I think that would work.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List for three cells (2003 )

    Sam,

    What about if I want to make the last column the dollar amount instead of Low, Medium, High. Any ideas. I want this to be a list to choose from based on two previous list. Thanks.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: List for three cells (2003 )

    Insert- name define
    Select "Amount"
    Change the "Refersto" to:
    <pre>=OFFSET(INDIRECT($A$6),MATCH($B$6,INDIRECT($A $6),0)-1,2,1,3)</pre>


    Then you can eliminate the "Value" column and the formula. It will not be needed.

    Note: there is a problem with this scheme. You must select Facility, Building, and amount in order. If you change Facility and do not reselect the other items your values will be in error since they are not "reset" by this scheme.

    Steve

Posting Permissions

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