+ Reply to Thread
Results 1 to 3 of 3

automate my hobby Lemoncello recipe and production sheet

  1. #1
    Registered User
    Join Date
    04-23-2020
    Location
    Brussels
    MS-Off Ver
    16
    Posts
    2

    automate my hobby Lemoncello recipe and production sheet

    All,
    I'm very passionate about creating Lemoncello in different recipe's. I've started to keep track in a basic excel file.
    Now I'd like to make sort of an automated file, but I only know the very very basic excel functions and formulas.
    I'm currently creating a masterdata sheet at the file, where I'll insert the ingredients their cost per kg or liter etc.
    The goal is to create multiple sheets, like recipe sheet, cost / sales sheet, expenses sheet and the silver lining would be a sheet with graphs on production and sales.
    In the recipe sheet, I want to be able to have drop down menu's where I can choose the ingredients then in a next column set the used quantity etc, based on those it then should project the expected quantity of the batch an provide the production cost in another sheet.
    All tips and help would be very welcome.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: automate my hobby Lemoncello recipe and production sheet

    The template shows how to create drop down lists and also create INDEX MATCH Formulas to automate the fill ins. You should set up the Master Data sheet with a 'unique' one word list and drop down lists to start the process.
    Attached Files Attached Files
    Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:

    Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.

  3. #3
    Registered User
    Join Date
    04-23-2020
    Location
    Brussels
    MS-Off Ver
    16
    Posts
    2

    Re: automate my hobby Lemoncello recipe and production sheet

    Hi guys,
    thanks for your advice and help so far... it got me a fair end in the right direction...

    What I have so far...
    Dashboards- never mind this one, this if for when I get the rest to work
    Sales – over here I want to keep track of what we’re selling and or give away
    Production – here we give our brews a name and batch number and keep track of what we mix and the volumes we produce
    Inventory – this gives a view of what we’ve put in bottles
    Production cost – speaks for itself
    Master OPEX – here we have the cost of tools and equipment we’ve bought
    Master Ingredients – the ingredients and their cost
    Master customers – here I want to create a customer list, that then later can be combined with sales

    My issues:

    I managed to get the master ingredients being the source for the production via drop down menu’s, since they return all the time.
    For the production cost I got it working as well, it takes data from production, and filling of bottles etc..

    For sales, here’s where it is getting too complex for my brain…

    What I want:
    The goal is to work via drop-down menu’s where the batch numbers show as what customers want to buy, then add the quantity.
    You’ll see in the inventory that it takes over the quantities of bottle sizes filled in the production tab. (inventory tab – start inventory)
    Inventory on hand should show the actual inventory, so minus what has been sold

    This means it should look on a bunch of variables; actual stock, bottle size, etc..
    The drop-downs should only show what is actually still “on hand”.

    Next phase then would be to have it calculate the total sum of customer order, based on the sales price set in tab production cost staring from column W and further, yet allowing to tick a box or set a mark and give away a 60ml bottle for free if we would decide so..

    Hope my explanation is somewhat understandable….
    Attached Files Attached Files

+ 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. [SOLVED] Hyperlinks From Pictures Sheet to Recipe Sheet
    By RChad in forum Excel General
    Replies: 2
    Last Post: 02-29-2020, 08:10 AM
  2. Output Txt file with recipe based on variable recipe entered into excel sheet
    By sdl2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-21-2019, 08:35 PM
  3. Excel is my hobby!
    By mrbirkmire in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-01-2018, 06:37 PM
  4. Automate Production sample input sheet with VBA
    By phbryan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2015, 08:30 AM
  5. [SOLVED] Problem in getting complete recipe/ Comprehensive recipe
    By brandedadnan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2015, 06:28 AM
  6. Excel VBA to automate Capex and Opex in a production profile
    By Baur in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2014, 11:46 AM
  7. Recipe Database save sheet
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2011, 02:06 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