+ Reply to Thread
Results 1 to 11 of 11

Complete Newbie Here, Grocery List Help

  1. #1
    Registered User
    Join Date
    09-15-2016
    Location
    Port Allegany, PA
    MS-Off Ver
    2011 (Mac)
    Posts
    4

    Complete Newbie Here, Grocery List Help

    So I downloaded this template

    http://www.freeplannertemplates.com/...-template.html

    And I really love how it's all laid out and how it's suppose to work. But it's not working!

    From what I can see I'm suppose to be able to write my meal plan on the first WorkSheet Labeled meal plan

    Then from there I would list out all the ingredients and how much ect needed under the second worksheet labeled ingredients

    Then I should be able to go to the fourth worksheet labeled shopping and click compile list and it will take all the ingredients that I need for what I put down in the meal plan and put them in a nice organized shopping list.

    Well That last part doesn't seem to be working at all. When I click compile it's telling me that the Macro can't be found.
    I also tried to refresh the pivot table but that doesn't work either..
    Any suggestions?

    I wouldn't mind starting from scratch and building my own Excel sheet either to function similar to this, but I've no idea where to start for that.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    If you want to start from scratch you'll need to build your own database sort of like the attached. From there pivot tables should be able to carry you along. Perhaps a pivot table based on a range overlaid on another pivot table.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-15-2016
    Location
    Port Allegany, PA
    MS-Off Ver
    2011 (Mac)
    Posts
    4

    Re: Complete Newbie Here, Grocery List Help

    I'm attempting to upload what I have done so far but can't figure out how to attach it?

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    Click on the Go Advanced Button. Then scroll down to Manage Attachments. That will give you a dialog box to navigate to and upload the attachment.

  5. #5
    Registered User
    Join Date
    09-15-2016
    Location
    Port Allegany, PA
    MS-Off Ver
    2011 (Mac)
    Posts
    4

    Re: Complete Newbie Here, Grocery List Help

    Did it work?

    [edit: it did! ok Uploaded what I have]
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    This looks great! You have the data laid out exceptionally well. I'll try to get on it today .

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    The underlying data for the pivot tables was not saved with the report. This complicates things a lot.

  8. #8
    Registered User
    Join Date
    09-15-2016
    Location
    Port Allegany, PA
    MS-Off Ver
    2011 (Mac)
    Posts
    4

    Re: Complete Newbie Here, Grocery List Help

    What can I do to fix it?

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    I think I found the data I need. It's on a hidden sheet called calculations in columns Y:AC. I'm going to have to do a bit of reverse engineering to figure out how they use the Multiplier. They have values like 1, 0.5 and #N/A. It's based on another named range.

    The key to the enterprise is the ingredients table. You maintain that and you should be in business.

    What I don't see and need to figure out is the connection between the meal plan and the ingredients to make the information in columns Y:AC on the calculations page. You are missing a macro called ShoppingList. This is what I'll have to try to recreate.

    So that's two things I have to do: reverse engineer the Multiplier (or not) and create a macro to create the table on the calculations page. I'm going to try to "plug it into" the existing system. I think once I figure out how to create this table, that the spreadsheet will run as planned. I do see some areas of improvement such as data validation for some things.

    This really needs to be in a database rather than Excel, but with some programming we can make Excel act like a database.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    I got the general gist of what the original programmer was trying to do with this. I decided to use that layout, but my own design.

    This program isn’t complete, but I’d like you to perform some QA on it before I go further. What the program is not doing is picking up the side dishes. I have to “extract” the program I got that does the main dishes and make it more general, but before I do that, I want to make sure it does what it’s supposed to do.

    Also to be able to test it, I had to remove a lot of data. The program was using ingredients that were not in the database. So I cut it back so we would have data integrity.

    Start on the Recipes Sheet. This is where you will enter in the dishes and whether they are main or side (this cell is validated) and how many people the recipe serves and an optional link to the recipe itself.

    Then go to the Ingredients Sheet. Enter in the dish (this cell is validated from the entries on the Recipe Sheet), the amount (a whole positive number), the unit of measure (this cell is validated from a list on the Loolups Sheet (Column A). You can add to this list. Don’t mess with the other information on this sheet. You can hide this sheet when not using it.

    Continue by entering the ingredient name and then how to round to the nearest unit. That is, if you are measuring a cup, you can measure to the nearest ¼ of a cup. On the other hand, you are probably not going to use ¼ can or ¼ loaf. Some things come in whole units.

    The program uses this information to round to the nearest unit of measure. So it might come out to be 1.2674321 tablespoons of something, but it will round to the nearest 0.25 tablespoons or 1.25 but since you can always do with more, but never do with less, if the rounded value is less than the required value, it gets “bumped up a notch.” In this case another ¼ tablespoon.

    There is a fallacy here: \ the combined plan may call for a total of 5.5 tablespoons of salt. Stores don’t sell salt by the tablespoon. Also you probably have all the salt you need on hand.

    Once you have these two tables filled in, then you can go to the Meal Plan sheet. I’ll give you a button in the future to clear this out so you can start with a fresh sheet.

    The date is validated to a date later than 30 days ago. I could just as well validate it to a date later than or equal to today. Let me know what you would like.

    Serves is limited to a whole positive number. This is the number of people you want to serve with this meal. This information is combined with the number of people the recipe serves to determine what you need. If the recipe serves 4 people and you need 1 cup of milk, but you are now serving 6, the program will calculate the need for 1.5 cups of milk.

    The main dish is validated to those dishes you mark as main dishes in the recipes.

    The side dishes are validated to those dishes you mark as side dishes in the recipes. As I mentioned above, I’m not looking at these right now. I will add them after we work the bugs out of this version.

    Then you can go to the shopping sheet. Click on the Compile List Button and everything should compute. That’s another advantage of limiting the data; it makes testing easier. It looks like I got the proper rollups.

    The list should print nicely in portrait mode.

    Play with it. Try to break it. Let me know what needs fixin’
    Attached Files Attached Files

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Complete Newbie Here, Grocery List Help

    P.S. I just thought of something else. This is a "freebie" since the data is already there. I can give you a utility to pick a meal and how many people you plan to serve, and it will pull the ingredient list for you. I can possibly add this to the Plan list. Select the meal, click a button and it will generate the list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. A complete newbie.
    By Adammulti in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2015, 12:06 PM
  2. Grocery list
    By jammerculture in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2015, 04:22 PM
  3. only print lines of grocery list with check mark
    By rhamblin in forum Excel General
    Replies: 2
    Last Post: 02-09-2013, 01:07 AM
  4. [SOLVED] Creating a grocery list in a new worksheet based on selected items in a master list
    By jacolli4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2012, 07:53 AM
  5. [SOLVED] Grocery List with Price comparison
    By mrJack808 in forum Excel General
    Replies: 8
    Last Post: 04-04-2012, 10:57 AM
  6. First Time Poster - Questions about Grocery List
    By Pastor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-20-2006, 03:38 PM
  7. a weekly grocery list and costs, available coupons and value
    By Excellente12 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-24-2006, 12:20 AM

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