+ Reply to Thread
Results 1 to 1 of 1

SUMIFS + LOOKUP? Inventory management combined with production schedule

  1. #1
    Registered User
    Join Date
    Winnipeg, Canada
    MS-Off Ver

    SUMIFS + LOOKUP? Inventory management combined with production schedule


    I'm trying to create an extensive book that will handle many tasks and calculations for my job. I'm a bit of an Excel noob unfortunately, and am having trouble getting figuring out what functions to use for one of the first core tasks I'd like it to perform; inventory management.

    What I'd like (specific to this) is to have one sheet for my 'task' schedule, one for receiving ingredients, one with my rolling inventory of needed ingredients, and one with the 'recipes' for these tasks. In each of these I have tables, with named ranges in these tables for things such as ingredients, dates, brands, etc. I'd like it such that by creating a schedule and tracking receiving, the rolling inventory will account for both received ingredients and scheduled output automatically. Summing the in and out is simple, as is the "subtotal" receiving/in, but I'm stuck on the "subtotal" out/scheduled production.

    I'm looking for something to the effect of:
    =SUMIFS((SUM(Ingredients1amount:Ingredients5amount...)*(COUNTIF(Task_Dates, Task_Dates<=TODAY()), (HLOOKUP(Tasks, Recipes, Recipe List, FALSE))), (Ingredients1:Ingredients5...), "name of ingredient for this row=(Indredients1:Ingredients5), Task, Task=Recipe))

    .. where for that ingredients' "out subtotal", it will look at the different scheduled tasks before and up to today, and look through those scheduled tasks' recipes and if that ingredient is a component, sum that as many times as that task appears in the schedule, and do that for all tasks that may or may not contain this ingredient. Evidently, the syntax and/or functions above are not working, perhaps because I've made them too circular.

    Am I on the right track? Is their a more straightforward way to go about this, if a I take a step back and reorganize my data (Pivot Tables)? Is their a wonderful function I'm missing out on? I'll need to be doing something similar to this throughout the book if it's to function as I'd like it too, i.e. being able to lookup and manipulate data in one table by matching it's header/category reference to input in another cell, nestled such that I can perform the needed manipulation and calculation.

    Attached is the sad work in progress.

    Thanks for your time, and any help is much appreciated.
    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. how to create a production schedule
    By freemason in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2013, 06:57 PM
  2. How to Schedule a Production run using excel
    By puddles01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 03:51 PM
  3. Schedule production help
    By liamsnodden in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2012, 12:53 PM
  4. Help: Production schedule on workday
    By led.dhany in forum Excel General
    Replies: 0
    Last Post: 10-20-2012, 07:02 PM
  5. Production and Inventory adjustment??
    By zx7r in forum Excel General
    Replies: 4
    Last Post: 01-13-2012, 10:41 AM
  6. Networkdays in Production Schedule
    By gibbsmachine in forum Excel General
    Replies: 2
    Last Post: 07-02-2009, 01:44 PM
  7. Inventory Management
    By Newtonboy in forum Excel General
    Replies: 5
    Last Post: 01-03-2006, 02:01 AM
  8. how to prepare production schedule
    By MUH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2005, 01:07 AM


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