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:
Item #: Description | On Hand | Vendor
544321 Absolute Vodka | 21 | S
132511 Gin | 12 | Y
154321 Chambord | 2 | Y
542954 Mandarin | 5 | S
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:
Item # |Description | Par | On Hand | Amount to Order
544321| Absolute Vodka| 10 (input)| 31 (=SUM)| 14 (= Par – On Hand)
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