Inventory Assistance

EDR · 2004-06-25T10:40:40+01:00

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

EDR
Jun 25th 2004

Anonymous

Enlightened

Reactions Received

2

Points

93,457

Posts

18,187
Jul 9th 2004
- #61
In a fit of stupidity I managed to delete your private message with the link to the data and your e-mail address. Would you send again please.
Derk
- 1
- 2
- 3
- 4Page 4 of 4

Participate now!

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

Facebook
Twitter
Reddit
WhatsApp
LinkedIn
Pinterest
XING

Users Viewing This Thread

1 Guest

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.

Participate now!

Share

Users Viewing This Thread