Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Best way to do a list (XP)

    What's the best way to choose from a "list" in Excel where I can choose from a list per row and it will complete a series of text for that line? I'll need it to be able to choose from a list for each row.

    Does that make sense?

    --KZ

  2. #2
    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: Best way to do a list (XP)

    It depends on the level of simplicity vs features you want.

    By far the simplest, but with the least features is data - data validation. Thi is especially true with multiple rows.

    The next is to use the combobox from the forms toolbar.

    The most complex, but allowing the most customization is to use the combobox from the control toolbox.

    The forms/control toolbox could be done with separate items in each row or code could be used use the same object over and over again. <post:=537,206>post 537,206</post:> has soem example code for this, some modifications were done at <post:=574,653>post 574,653</post:>.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    Thanks Steve,

    What am I doing wrong? When I open up those files, it shows the drop list, but when I put the cursor over them, it just gives me the cross hairs to move it and it doesn't open up the list.

    Also, what is "data validation"?

    --KZ

  4. #4
    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: Best way to do a list (XP)

    It sounds like you are in "design mode". Open the control toolbox (View - toolbars and check "Control Toolbox"). On the toolbar, click on the pencil/triangle/ruler icon to "Exit design mode"

    Concerning "Data Validation" you can read the basics on MS MVP Debra Dagliesh's Excel -- Data Validation site.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    Ahh, thanks! That did it.

    Now I can start designing...what I need to do is if the contents of the drop box then equals xxx, then the contents of the next column needs to be equal to xxx.

    One column will be the name and the next column the price corresponding to the name.

    Can you help with that?

    --Ken

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

    Re: Best way to do a list (XP)

    If you have a table containing names in the first column and the corresponding prices in the second column, you can use a VLOOKUP formula. Say the cell with the dropdown is H4, and the 2-column table is in P1:Q38. The following formula in I4 will look up the price:

    IF(H4="","",VLOOKUP(H4,$P$1:$Q$38,2,FALSE))

    (The IF is used to avoid returning #N/A if the user hasn't selected a name yet)

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    Attached is the file I'm trying to work on. Can you help?
    It's giving me an error message.

    I had already worked on simply using the IF statement. I may have to update it to the VLOOKUP. It's like it's not reading the data in the droplist in A:2 at all.

    --KZ

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

    Re: Best way to do a list (XP)

    You have used a combo box from the Forms toolbar; Steve's code was meant for a combo box from the Control Toolbox, I think.
    I have removed the combo box and the code that didn't work, and used Data | Validation instead, and a formula as in my previous reply. See the attached workbook.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    Awesome! Thanks!! I'll play around with that some.

    (I need to head out for a few hours, so I'll check on it more later.)

    --KZ

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    This is working out great. Thanks everyone!

    Now, is there a way to not show the #VALUE! at the result of a formula? I have the rows adding up the data, then a grand total at the end. If there are any #VALUE! in the cells, it won't display the grant totals.

    Thanks
    --Ken

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

    Re: Best way to do a list (XP)

    You could use this in B2:

    =IF(ISERROR(VLOOKUP(A2,Prices!$A$2:$B$7,2,FALSE)), "",VLOOKUP(A2,Prices!$A$2:$B$7,2,FALSE))

    and fill down as far as needed.

    Question: when do you get #VALUE?

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    Attached is the updated spreadsheet. Your new formula didn't show any result in the cell, even though it calculated the contents, but that's not where I'm getting the #VALUE!. it's indicated in the spreadsheet though.

    Thanks
    --KZ

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

    Re: Best way to do a list (XP)

    The problem arises because multiplication (*) doesn't accept an empty string "" as argument. You can keep the original formulas in column B. In D2, enter this formula:
    <code>
    =IF(ISERROR(B2*C2),"",B2*C2)
    </code>
    and fill down to D27.

    BTW shouldn't it be "Grand Total" instead of "Grant Total"? Or is it really a grant?

  14. #14
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to do a list (XP)

    PERFECT!
    BTW, thanks for checking my spelling too. Doh!

    Final question: I have protected the form so that only the needed areas can be filled in. I had to leave the drop down area unprotected. Is there anyway to make it so that a person can't type over the cell where the drop down is which would remove the list?

    --KZ

    P.S. Also trying to figure out where to add the number of drop downs per cell and also where you added the comment popup. [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Best way to do a list (XP)

    Select the cells with dropdowns (A2:A27 on Bid Sheet).
    Select Data | Validation...
    The first tab in the Validation dialog specifies that the user can select from a list. The list is the named range OptionList.
    The second tab specifies the text for the popup that is shown when one of the cells is activated.
    If you don't want the popup, clear the check box and/or clear the text.
    The third tab specifies the text for the error message that appears if the user tries to enter a value that is not in the list.

    If the sheet is protected, the user cannot remove validation. But it is possible to paste an incorrect value into a cell.

    The named range OptionList is defined in Insert | Name | Define...
    Click on the name, then click in the Refers to box to view or change its definition.

    PS The text near the bottom of the Bid Sheet says Platering instead of Plastering.

Page 1 of 2 12 LastLast

Posting Permissions

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