+ Reply to Thread
Results 1 to 3 of 3

Creating a grocery list in a new worksheet based on selected items in a master list

  1. #1
    Registered User
    Join Date
    07-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Creating a grocery list in a new worksheet based on selected items in a master list

    Looking for some help as I know this is way over my head.

    I've created a master list of items I buy and added other basic info about them like price, aisle, etc (a sample is attached). I want to add a button (macro) that will allow me to create a list (in a new sheet created by the macro) of those items where the quantity > 0. The new list would include all cells associated with that item (the row) such as name, quantity, price, extended price. Then at the bottom of the new list the "total w/tax" (a cut and past of the value or whatever way is easiest). I put it off to the side of the master list so it's location would be fixed.

    I'm also wondering if the sheet that's created can be named based on the current date. And any other suggestions would be welcome; I'm guessing there's probably a better approach to this or something I'm leaving out.
    Attached Files Attached Files

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Creating a grocery list in a new worksheet based on selected items in a master list

    Personally I would steer well clear of spin-buttons and other controls.

    What did you want to do with the spin-buttons?
    Remember as your sheet grows/shrinks you'll need to add/delete another control, doing this will give you nothing but grief.

    Command buttons can easily be simulated with a cell and the Worksheet SelectionChange Event.

    I've used a named range "Totals", a helper column (Sheet "Store" Column A"), formula and conditional formatting to automatically create your order.
    As it stands the result will handle up to 20 individual items, to extend this select B25:F25, or any range above, and drag the formula down, the formatting will go with it.

    This way VBa is kept to an absolute minimum, it's only required to reset Sheet "Store"

    Add your quantities in Sheet "Store" to see the result in Sheet "Order"

    You can change the Tax Rate in Sheet "Store" I6
    Attached Files Attached Files
    Last edited by Marcol; 07-28-2012 at 06:46 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    07-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Creating a grocery list in a new worksheet based on selected items in a master list

    Thanks Marcol! That solution is much simpler than the path I was going down.

    I understand your point about the spinners. It was already tedious to add them (though I used a macro to create them for the entire range, it's annoying to move and size them to get them set up). My thought was to have the final solution only require mouse clicks, which it looks like I can still do if my wife requires it.

    Thanks again for your help. Problem solved!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1