Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a list of items on a spreadsheet that has several dozen items on a drop down list, then one next to it with quantity.
    What I'd like to do is once that pull down is chosen, to be able to total the cost of that item times the quantity in the next drop down into another cell.
    Is that possible? if so, it will be extremely helpful.

    Thanks!

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Will the attached do what is needed?
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by CrazyKZ View Post
    I have a list of items on a spreadsheet that has several dozen items on a drop down list, then one next to it with quantity.
    What I'd like to do is once that pull down is chosen, to be able to total the cost of that item times the quantity in the next drop down into another cell.
    Is that possible? if so, it will be extremely helpful.

    Thanks!
    See attached - Column A has a drop down list, Column B has a lookup to prices, Column C is where quantity is entered and Column D gives Quantity times price.

    Regards,
    Maria
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Fantastic! Thanks. That will save me a lot of time. Both of these will work great.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK, now I'm not sure how to edit the drop down to tell it MY cells to calculate. I thought I knew what I was doing, but I'm working at home on a different computer and I must have something turned off or I don't really know what I'm doing. Can you clarify how to edit it? The other spreadsheet I had, I created the drop down, then protected the sheet for the drop down to work correctly. When unprotected, I just right-clicked the drop down to get to the format control. Were these done differently? Thanks!

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    For my example, if you want to change the Item descriptions, change them in Column A, then the drop down list in Cell F2 will change automatically. If you want to change where the list gets its data from, change the data source.

    [attachment=91211:Validation list.jpg]

    Hope that helps.

    Tim
    Attached Images Attached Images

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks! Now, can you tell me how to make the pull down to be able to show more lines?

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I made a couple of changes for ease of use. I gave the items a Range Name of ItemList.
    To add new items, just insert rows somewhere within the range named ItemList. I also changed the Validation Source to refer to ItemList.
    Results should be if you add items, change item descriptions or change unit costs all calcs for Cells F2 thru I2 should be automatic.

    [attachment=91215:Validation list A.jpg]


    A revised workbook is attached.

    Hope that helps
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks. What I mean is to have the drop down list show more of the list without having to scroll the list.
    Is there a way to have it also increase the font size of what it shows in the drop down?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No, you would need to have a control on the worksheet for that. See Debra's page here.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Crazy,

    Check this out.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by rory View Post
    No, you would need to have a control on the worksheet for that. See Debra's page here.

    Quote Originally Posted by RetiredGeek View Post
    Crazy,

    Check this out.

    It seems that both Rory's and RG's links go to the same info. Is there any info difference between the two?

  13. #13
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm not figuring this out. I was able to get everything to work correctly with the DataValidation...all except for the further editing (font size, drop down list number, etc.).
    I am using Excel 2007 and none of the informaiton matches up with the instructions above on the link.
    Am I needing to start over now and use a different method? Sorry, I'm confused.
    I have attached what I have so far (blanked out my data for the most part) if someone can help me.

    Thanks!

  14. #14
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by CrazyKZ View Post
    I'm not figuring this out. I was able to get everything to work correctly with the DataValidation...all except for the further editing (font size, drop down list number, etc.).
    I am using Excel 2007 and none of the informaiton matches up with the instructions above on the link.
    Am I needing to start over now and use a different method? Sorry, I'm confused.
    I have attached what I have so far (blanked out my data for the most part) if someone can help me.

    Thanks!
    CrazyK,

    The further editing (Font size, Drop Down List Number of rows, etc) only applies to combo boxes - you are only using Data Validation in Drop down Lists not combo boxes. You would need to follow the earlier examples in the earlier links on how to create and format combo boxes.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  15. #15
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Can combo boxes also do what I'm trying to do with giving the total in another cell?
    Is anyone else using Excel 2007? Those instructions are not for that version. I don't see a "Control Toolbox". It might be under Insert | Controls. There are two areas: Forms Controls and ActiveX Controls.

    One choice will allow some editing of where the drop list comes from and how many rows. The other looks like what they are talking about, but I can't see how to get to read from any other data for the list. Then when I click on the Design Mode button the list actually goes away. Even on their sample page I can't figure out how they made that drop down lock to that cell, nor how it reads from any other data.

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
  •