Inventory Assistance

    I have (what seems to me) a very complicated request. I’m trying to do a custom workbook without using VB (if possible). I’m trying to design an efficient beverage inventory system for a restaurant.


    Here’s the basics of my design idea before I start:


    1. It will consist of three worksheets within ONE WORKBOOK.


    2. MAIN PAGE. The main page will have many varying and duplicate entries throughout. For example: The same worksheet may have the same item number multiple times but in varying rows. And, as I would like to be able to easily insert or delete a row as items are needed, I’m leaving this area vague.


    3. ORDER SHEET. The order sheet will automatically itemize and summarize all items (and any duplicates on the main page) into categories divided by vendor, and the specific item each vendor carries.


    4. ALPHABETICAL PAGE SUMMARY. This page will itemize all products in alphabetical order and their on hands status summary so an accountant can easily enter such data.


    I know how to do this already, but only the hard way, to reference each row (or cell) on the other pages to CURRENTLY EXISTING rows (or cells) on the main page. What is beyond my knowledge is how to have the other pages automatically add, delete or sort data based on any and all data on the main page (whether added or deleted later). That is the key I’m looking for. I want the end user to be able to add or delete rows only on the MAIN PAGE, as needed, but the other pages to see, rocognize and reflect the changes in their output.


    Here’s a hypothetical run down:


    MAIN PAGE:


    Room #1


    Item #: Description | On Hand | Vendor


    544321 Absolute Vodka | 21 | S
    132511 Gin | 12 | Y
    154321 Chambord | 2 | Y
    542954 Mandarin | 5 | S


    Room #2


    544321 Absolute Vodka | 10 | S
    154321 Chambord | 3 | Y


    As you can see there are duplicates of the same product(s). The reason there are duplicates is because different rooms (or bars within the same restaurant) have different amounts of product. On the Order Sheet, I’d like it to be separated by vendor and look like:



    Vendor S:


    Item # |Description | Par | On Hand | Amount to Order


    544321| Absolute Vodka| 10 (input)| 31 (=SUM)| 14 (= Par – On Hand)



    Vendor Y:


    Item #| Description | Par | On Hand| Amount to Order


    154321| Chambord | 10 (input)| 5 (=SUM) | 5 (= Par – On Hand)|



    On the alphbetical sheet, I'd like any and all products from the main page to be summarized (the total number of on hands from all duplicates) and sorted that there is only one occurrence in alphabetical order, so for example:


    544321 | Absolute Vodka | 31
    154321 | Chambord | 5
    132511 | Gin | 12
    542954 | Mandarin | 5



    I’m sure this would require several arrays and other things beyond by scope of knowledge, but I do learn quickly and move around in Excel quite fluently. I just need a little guidance and I can usually find my way.


    Thanks in advance for your help. EDR

    You seem to be making life unnecessarily difficult....


    the simplest solution AFAIC....


    Add a field for Room# in your MAIN PAGE so that it is set up like a database...


    Then you can use a fairly basic Pivot Table to summarise the data in pretty much whatever way you like...

    What if I have separate sheets for "ARRIVALS/RECEIPTS" and "ISSUES/CONSUMPTION", and maybe another one for "ORDERS"? Can the Pivot table summarize the data residing in separate sheets? I've tried using multiple ranges option, but the output looks nothing like it ought to.


    This is something that's been gnawing at me for over a year now. (At the moment, I'm managing my master sheet with SUMIFs. However, each time a new item is received, I have to manually add it on the master.)


    Would appreciate your help.


    Regards,


    m


    Would it be possible to provide an example? Don't want to take advantage here, just not quite sure what you mean. Also, while pivot tables are neat, they have to be refreshed and created. Hmmm?


    The concept of "Shelf to Sheet" is the most widely used and easiest form of inventory when one has multiple bars with repeated products throughout. Then one can go from room to room, left to right, top to bottom, etc., in a systematic order and record the amount of each product.


    The problem is having another two sheets that take all the data from the one and:


    1. Summarize
    2. Categorize
    3. Consolidate
    4. Alphabetize by Vendor


    AND ... still have the ability to add (insert or delete rows) on the main page, yet still have the other pages work.


    Ideally, one main page with headings regarding the various rooms room would be easiet, as just before inventory I can print out the entire sheet, and all 20+ pages will print in order of the rooms we walk into. Of course, doing this is the easy part. I can hand enter the data, or in the future add or delete rows as needed for new product additions or removals. Perhaps on this page, I can also enter in a 2 letter code for each item, indicating the vendor, i.e. "WW" - Wine Wearhouse.


    As stated in the initial post, if an "ORDER" page can search the entire MAIN PAGE for any duplicate items, sum their amounts entered, and then categorize only one entry for each product under the appropriate vendor, that would be awesome.


    If anyone could provide me a simple spreadsheet with a brief example that functions, I will certainly take it from there and expand it to hundreds of entries and make it look professional. Thank you so much!

    It seems to me there are two separate considerations at play.
    1) Ease of taking inventory. For this, a separate worksheet for each room would seem best or perhaps one big sheet with sections for each room.
    2) Ease of summarizing results. For this a central database, as Will suggests, to feed a pivot table (easy to create and refresh).


    Solution: Have both with a macro to create the database (and refresh the pivot table) from the individual room sheets (or sections).


    Is there a reason you want to avoid macros? it should be relatively easy to create and run quickly. Before creating an example, would this be a way to proceed for you?

    Yes --- Thank you!


    Quote from Derk

    It seems to me there are two separate considerations at play.
    1) Ease of taking inventory. For this, a separate worksheet for each room would seem best or perhaps one big sheet with sections for each room.
    2) Ease of summarizing results. For this a central database, as Will suggests, to feed a pivot table (easy to create and refresh).


    Solution: Have both with a macro to create the database (and refresh the pivot table) from the individual room sheets (or sections).


    Is there a reason you want to avoid macros? it should be relatively easy to create and run quickly. Before creating an example, would this be a way to proceed for you?


    Hi Derk:


    Thank you so much. Yes, you are correct, the two separate considerations are at play. One big sheet with sections for each room is perfect - several rooms may duplicate a particular product. Via an input on that main page, there will be a space for "on hand", such as Section 1: Absolute .3, Section 4: Absolute .8, Section 5: Absolute 1.5 (.10 equals 1 by the way at least for this kind of inventory). I'd like the summary pages to SUM all the various absolutes it finds.


    The "central database" is where I'm clueless (in terms of Excel), and with respect to Macros, I'm not against them at all, I'm just not very macro savy - hence my comment to stay away from VB. I know VB is very powerful, I'm just not a true code geek (wish I was). With a little coaching and/or directions I could probably figure it out and/or duplicate some initial basic code (lots of copy/paste).


    I used Pivot tables alot in the past, although it's been about 2-3 years, so I'm a little foggy. I remember it being a little limited, and with literally hundreds of entries, I hope it won't bog it down.


    I would love any example workbook someone would like to present to get me started! Thank you. If there's alot of VB code inside a Macro, I might be a little lost, but again with the proper initial coaching, I should be OK.


    At this point, I'm open to ANYTHING to get the job done within the confines of Excel. The company would not invest in File Maker Pro, SQL or any other more extensive, expensive data base. I know the company does not even have Access, and the people I know that can use Access hate it! I'm definitely more comfortable in Excel than Access anyway. At least with a Macro you can save the file with the Macro, and any company computer that opens it (that doesn't have extreme security) should be able to use it.


    I'm OPEN to anything - no biases here. Tks. EDR :)

    Attached File


    Derk (or anyone else):


    See attached file for a VERY SIMPLE run down. As you can see, I know how to do this the hard way! Very hard. The problem is, if anyone adds or deletes a row on the main (INVENTORY) page, it may screw up all the other pages and how they reference each individual cell on the INVENTORY page. I want to get around that and be able to populate the other pages very easily. I also want to make this as computer-dumby friendly as possible, so that the novice Excel user who decides to add and delete any product rows on the inventory page won't have to worry about the other pages - I would hope they'd update automatically.


    Also, the company does have a specific product number for each product, which must be part of the sheet. Maybe easier to call that instead? The only thing is, in terms of alphabetizing on the AOM page maybe it should call the actual product name in certain headings?


    Thanks again! EDR :)

    Appreciated!


    Quote from Derk

    I'm pressed for time this weekend, but if no one else offers help in the meantime, I'll give you something to try Monday.


    I'll wait patiently for anyone who would be kind of enough to get me started, whether today, tomorrow, or the next day. I would already be doing it if I wasn't fuzzy on the database suggestion you and Will provided, and of course, getting a Macro going. I'm willing and eager. Thanks again! :)

    I am working long hours this weekend too, but will check in to this thread every 12 hours or so. Thus, if there's a delayed response on my end, I have not given up on this thread. Also, I'm running on West Coast (Los Angeles) time. Thanks again!

    Hi All (and/or Derk):


    I've just gotten in from a long weekend, and have the next two days off - yeah! I'm still open to anyone who may be willing to get me started on this one.


    Thank you so much! EDR :thanx:

    I have just had a quick look at this and would go with a pivot table, which can be set to refresh on opening from the PivotTable options. You could have a separate sheet, or separate workbook for each room if you like. This would be set out the same as the database and filled in with data. It would then be a simple case of copying and pasting the data into the database. I have used a Dynamic named range for the Inventory data. Try copying the new data from Room 3 and pasting into the Inventory, then open the Report


    Let me know if this is any help. As Derk says you can easily set up a refresh macro, and maybe a simple Data entry userform.

    Roy has shown you a way to update the database by pasting. The attached does it by macro. I have also created some new sheets for dynamic tables of vendors and products. If you use these as master lists, then the inventory and order sheets can refer to them by formula to avoid typos (see the vlookup formulas in those sheets). You may want to expand these tables to associate vendors with products if that will be a unique relationship.


    The pivot table is refreshed when the macro runs to build the database. It can replace the AOM sheet, which I did not update. The macro assumes that the Room titles on the inventory sheet start with the word Room, since you are relatively freeform in this sheet. It also assumes there is nothing else in column a of that sheet other than the headers and the product numbers for the inventory.


    Give the workbook a try with some new additions and see if it serves your purpose. It can easily be modified as needed or to fix bugs.

    Derk:


    You are brilliant! Thank you. There’s no way I could have figured out how to write the VB Macro you put together. As previously stated, I’m not Macro savy.


    In any case, this will work. I know the end result will be perfect! However, I’d like to ask for two minor modifications as per my original example, and then I’ll be on my way filling in all the data. Please let me know how I can repay you, and send me a private email if need be.


    Here’s what I’ve changed/noticed (see attached revision):


    [INDENT]1. PRODUCTS FORMAT. I added “products_2” worksheet only to show you how the company sends out product listings every quarter or so. This is the exact format they use in Excel and is the actual product listing they sent me. Ultimately, I’d like to paste their product listing from their spreadsheet directly into the “products” spreadsheet you created. This way, I can call their "Item #" for each product as needed. I tried pasting it into your current products and of course it generated a ton of errors – my bad - so instead I created "products_2" just to show you. Maybe the Macro can link to the format provided in Products_2 (ultimately that will be deleted and renamed to “products” as you have it now). I like your Vlookup function, and would ultimately like to call the “ITEM #”, such as 51234 “Godiva Dark” from the Products page and have that feed everything else. [/INDENT]


    [INDENT]2. AREA. I changed your VB code to reference “area” instead of “room” because there are actual several “areas” within a room, and this will be easier. I got this working fine – just letting you know that is the only change I made to your Macro code.[/INDENT]


    [INDENT]3. PARS. As per the original spreadsheet I sent, I need to have a pars spreadsheet, or something, where I can establish the MINIMUM PAR for each product. This is very important for the order page. I don’t care if it’s its own “par” spreadsheet, or if when I enter the product into the INVENTORY page, there’s another column called “par.” ( I entered an example if revised sheet - see attached). Then, when I do the order sheet (or refresh the pivot table), the SUM of total “par” minus “on hand” will tell me how much to order. I simply call my vendor and tell them the amount to order. If my total on hand for Absolute in all the rooms is 6.5, and my minimum total par is 8, I obviously need to order 2 (round up to nearest whole number as you can’t order ½ a bottle). Preferable, if the value is ZERO, or if the value is a negative number because there is more on hand than the par, I'd like to leave the cell (or field) blank. [/INDENT]


    [INDENT]4. AOM. I deleted the AOM page as you suggested, and set up the pivot table as I like it under the “pivot” spreadsheet you created. I’ll print this out for the accountant to enter data. Perfect.[/INDENT]
    My only two remaining big issues are the products format and the pars, and then I'll be on my way. I tried playing with both, but only screwed things up, so it's a bit above my area of expertise. Thank you again. After this post, I’ll be out of your hair! I appreciate your time and patience on this. Again, if I can repay you in any way, please let me know.


    :thanx: EDR


    P.S., actually, the file sized is 394kb, and the limit to upload to this site is 293kb. I'll post it on another site. You can download the file from here:


    http://www.natt.net/temp/inventory_4.xls


    :rock:

    Try this version. I deleted some rows on the Product sheet so I could post it here, but you should be able to just paste new listings over the old listings and all will be well. For the Par and order amounts, see the new Pars page. Some of the entries are links to other pages. The Order sheet has a trial pivot table based on the Pars sheet data. It is not yet set to refresh automatically. Try this one out and post back if you need help with the final refinements.


    (By the way, my help or advice is freely given on this forum.)

    You Rock!


    Derk:


    You are very kind! You rock! The spreadsheet works perfectly! :thanx:


    My only concern is that the pars tab requires alot of duplicate entry (by hand) with respect to item # and product. I do see that some of the fields have Vlookup and sums, and I see the reason you did this so that a TOTAL par can be established up front, which ultimately would be nice.


    Nevertheless, to keep all of the human manual input on ONE page (INVENTORY) so that it's not confusing for a novice who may be using this program - notice the Par column I created in the version you already have (on the INVENTORY page).


    Would it be possible to enter a par in that column instead with any new product entry, then have a sum of the pars calculated for each product in each area, and still have the difference posted on the order page as it is now?


    Also, as per original requests, can the output pages (or pivot tables) - everything but the inventory page - be in alphabetical order, as per product name? The pivot page will be printed out for accountant, and he prefers it in alphebtical order, even though the item #'s vary. And, printing out the order sheet, which I will create additional pivot tables, categorizing per vendor. All of those would be nice to have in Alphabetical order.


    That would be my final revision requests for this project! I promise. :thanx: EDR

    The attached adds the par figures on the inventory sheet to the database, so the pivot table on the pivot sheet now shows the order numbers. I put the product names first in the pivot table so they will be in alphabetical order. As the table now stands, the order amounts are only integerized on the totals. If they should be intergized instead by area, that can easily be done so let me know.


    Whether to use the Par sheet is up to you. Perhaps it should be modified so that standard info for each produc # can be entered once and then the inventory sheet could draw from it.


    I notice the Products sheet has costs on it. Should those be used anywhere?

    RE: Bug?


    Hi Derk:


    Thank you. I feel like I’m taking advantage now, so if you feel that I am, please let me know, and once again, if I can repay you in any way, please let me know.


    Please download the updated version. I didn’t change any of your code, just re-arranged some of the pivot tables closer to the ultimate format they will be in. Also, if the following is not a bug, but my wrong usage, I apologize in advance.


    BUG ONE:


    I’ve defined “Henry’s”, “Wine Warehouse” and other vendors, yet on the ORDER SHEET, the drop down arrow only provides a choice to show data for:


    Southern
    Young’s
    Allied


    There are six vendors defined, and I’ve made sure I coded them on the INVENTORY PAGE, yet only three show up on the order sheet pivot table. I’ve made sure to refresh all pivot tables, even saved, and reloaded the program (have set the pivots to refresh on open), but still no go? What am I missing?


    Now that I think about it, the PARS tab is not updating anymore, and only shows the three vendors. Perhaps we should get rid of the pars tab altogether, since I’m entering pars on the INVENTORY SHEET NOW. I think the pivot tables must be referencing the PARS tab.


    ADDITIONAL REQUESTS:


    [INDENT]1. Renaming the “pivot” page to “AOM” may be more user friendly for those of us in the company that understands that and may be using this. I’m scared to rename it because the VB code may reference "pivot" and things may go haywire.[/INDENT]


    [INDENT]2. To answer your question about pricing and quantity, yes I suppose that would be a good idea. If somehow the F column (unit quantity) on the Products page “BT”, etc., could show for the corresponding item # in column D of the AOM (pivot) page, this would be great. Accordingly, if the G column (prices) on the Produts page could show up under the corresponding product in the E column of the AOM (pivot) page this would be great too. Then, if the pivot table on the AOM page shows the SUM of the total dollar value of the product ON HAND (this is a great number to have). [/INDENT]


    [INDENT]3. With respect to your comment about integers and alphabetizing, the AOM is used to looking at the product number first on the far left (item #), coding that in by hand, confirming the product to the right of that (alphabetized), and then the amount. Thus, placing the product NAME on the far left may throw them off. As you can see I changed it back. If there's anyway to alphabetize by name, even though the product will be listed in column B that would be great. The item numbers do not beed to be in numerical order, just alphabetizing the names and having the right item number show to the left of the name.[/INDENT]


    [INDENT]4. Finally, on the order sheet, if column C could show the appropriate unit price from the products page, and then if column D could sum column C * column B on the order sheet, I should be able to match up the invoice total from the vendor with the price it should be form the company approved pricing on the products page.[/INDENT]


    http://www.natt.net/temp/inventory_New.xls


    :thanx: EDR

    Here's another version with a new name to avoid confusion. The "Bug" wasn't really a bug as the Par sheet was abandoned and that pivot table drew from it. I've redone things so that both pivot tables now draw from the same expanded database. Also the order amounts now integerize at the area level instead of at the total level (i assume this is desirable).


    One of the Pivot table limitations is that automatic sorting goes from left to right so you can't have the AOM table sort by product name unless it is either the first column or you are willing to do a manual sort name by name.


    I have put the prices in. I notice that some are to three decimal places and at least a few are quite low (0.01 per bottle for example).


    Don't hesitate to ask for more improvements or how something works. At some point this will be yours to maintain, so now is the time to get it as you want it.

    Anomaly?


    Hi Derk:


    I think this will do it! No more add-on requests. :thanx:


    See the following image:


    http://www.natt.net/temp/anomaly_1.jpg


    I added a column to the pivot table on AOM page (so when the AOM enters in the data they can compare and confirm the actual per unit price) and notice ABSOLUTE showing $41.90, yet everywhere else it's correct at $20.95.


    Also, the BENEDICTINE showing at $61.80, when it should be $30.90 as it is on the database page? What am I missing? Thank you. I really do appreciate your time and effort on this. EDR

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login