+ Reply to Thread
Results 1 to 4 of 4

Pulling Information from one page to populate columns and amounts in another page

  1. #1
    Registered User
    Join Date
    10-29-2017
    Location
    New South Wales, Australia
    MS-Off Ver
    2016
    Posts
    10

    Question Pulling Information from one page to populate columns and amounts in another page

    I'm currently trying to eat healthier with a family of 5. I have created (or trying to create) an excel Meal Plan that has alot of useful information and recipes.
    What I'm currently attempting to do is create a workbook with 7 separate books inside the one excel file.

    Breakfast - Lunch - Dinner - Snacks - Meal Plan - Ingredients - Shopping List

    Each category is it's own book. What I've done is in each of the first 4 books I have listed the receipes, names and calorie amount in three separate columns, then in the meal plan I have a 7 day layout (horizontal) and breakfast, lunch and dinner (vertically) in columns with this I have used a LIST function to have the ability to choose from a dropdown list of recipes that are populated with their respective BOOK (breakfast, lunch, dinner, snack), in Ingredients I have the first column as the name of the Recipe and then in the second column I've got the food/ingredients name and in the third column I have the amount required.

    What I'm looking for is a way to populate the shopping list with what is selected in the Meal Plan section.

    EG:
    Step 1: Meal Plan Book - If on Monday I choose Healthy Nuts for Breakfast
    Step 2: Shopping List Book - It should automatically get all the ingredients from the Ingredients Book and add them to the list and add the QTY required.
    Step 3: (Only If Possible) Meal Plan Book - I choose Healthy Nuts for Breakfast on Tuesday
    Step 4: Shopping List Book - Automatically gets all the ingredients from the ingrediants book and adds them, if possible however if it notices that the food/ingredient is already in the list it should add the amount required not add the food/ingredients again.

    If anyone could help figure this out it would be greatly appreciated.
    Last edited by AaronG85; 10-29-2017 at 09:17 PM. Reason: Change title to comply with Rules

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Pulling Information from one page to populate columns and amounts in another page

    Hello,

    There are some templates out there that do similar things. But if you want to roll your own, that's fine, too. Using only worksheet formulas, this might not be doable, or require very sophisticated, complex formulas.

    It would be possible to write some VBA that processes the Meal plan book.

    If you have an edition of Excel that includes Power Query, I'd opt for a Power Query solution and build your shopping list with a pivot table that is fed from data that Power Query gathers from the data input. Power query can easily combine the data from the Meal Plan with the data from the recipe sheets. I assume that the number of ingredients varies for each recipe. With proper data layout this can be a piece of cake (pun intentional) for Power Query.

    (They're sheets, not books, by the way. The file is the workbook and the tabs are the worksheets)

    So, which approach do you prefer?

    cheers, teylyn

  3. #3
    Registered User
    Join Date
    10-29-2017
    Location
    New South Wales, Australia
    MS-Off Ver
    2016
    Posts
    10

    Re: Pulling Information from one page to populate columns and amounts in another page

    Power Query sounds like the way I would like to go, is there somewhere I can read up on this?

  4. #4
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Pulling Information from one page to populate columns and amounts in another page

    Quote Originally Posted by AaronG85 View Post
    Power Query sounds like the way I would like to go, is there somewhere I can read up on this?
    Hi Aaron. I have just started using PowerQuery. I learned the basics by searching Microsoft's help.

    Good luck!
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

+ 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. Have to delete entire column if specific work exist in row
    By rizva9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2015, 12:06 PM
  2. [SOLVED] Check First IF Sheet Is Exist With The Name Of Cell K7 If exist Then run the code
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2015, 10:08 AM
  3. [SOLVED] Syntax Compile Error - How to work around this (file path doesn't exist)
    By Harribone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2015, 08:19 AM
  4. [SOLVED] how to run macro or msgbox when Worksheet does not exist/exist
    By herukuncahyono in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2013, 10:54 PM
  5. Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  6. Replies: 3
    Last Post: 03-11-2006, 02:50 PM
  7. Replies: 0
    Last Post: 03-10-2006, 05:55 AM

Tags for this Thread

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