Inventory, Invoice and Order Forms(Long read)

[83Rob83]

I apologize, I get a bit winded on this one. The bold letters are the start of each question if you just want to get to the point. You're probable going to need to download the two files to better understand my ramblings.

Thanks to this Forum I recently learned how to use Power Query and combine 1000's of invoices together and add up the quantities of all the merchandise we sell at work. In case you're wondering, I'm an Officer at a prison and I run the Commissary (Inmate Store) there. Our old inventory program expired and instead of renewing the license, they left it up to me to create something out of nothing(Excel and a network drive). The invoices I have made work wonderfully (See attached) and much better than the inventory software we were using so I must say, i'm very impressed with Excel. I am limited to Excel 2010 so I can't use Power Query but I've managed to find a way around that for now by uploading to a cloud and using my personal computer at home. I have few more problems I want to solve so hopefully you guys can help me again.

I'll give you a "quick" rundown of how we operate. About 10 or so staff members create invoices from all over the facility so the Excel Invoice file is shared on a network drive. Each morning they copy the file over from the network drive to their desktop and begin turning the order forms (The inmates hand write their names , ID's and quantities next to the items they would like) into Excel invoices that add everything up. The Invoices do most of the work for us as soon we enter their ID number, their name, pod and available balance appear from another file that I export (iCash.xls) each morning from another program. Sometimes the data doesn't update and once you copy the Invoice from the network drive to your desktop, the vlookup formula that points to the exported file(iCash.xls) changes from having a network drive address to a C:\user\ drive address. The file has [] around it so I don't think it matter but my 1st Question is, is there something else I need to do to make sure that the Invoice is constantly linked to the exported file?

Each items has a limit and by using data validation I was able to make a Error message appear when that limit is exceeded. I wanted to do the same thing once the total dollar limit is reached or when they run out of money however the data validation doesn't seem to work with cells that have formula's so instead I use conditional formatting which turns the sheet red once they reach the specified limits. Question # 2, is there a way for data validation to work with a cell that gets it data from a formula?

Once the Invoice is fill out, I added two macro buttons, one automatically saves the file to the network drive using their Name and Date as the file name and also prints 2 copies. The other button clears the cells so they can start a new invoice. What actually prints is a printable version of the Invoice that only contains the products that have quantities next to them. So instead of having all 100 items printed out with only 20 - 30 having quantities entered next to them, only the 20-30 items are viable on the printed invoice which makes pulling the merchandise from the shelves faster with less errors and it also saves us on printer toner. The only problem is there are a lot of blank cells in between the items because the table is just a mirror of the full invoice minus all the non-ordered items. My 3rd Question is, is there a way to keep all the items together or hide the blank spaces?

Since I have the ability to track my inventory now using Power Query to tell me how many items I have sold, I would like to be able to have the Invoice prevent us from selling items we do not have. Since people all over the facility input the order forms, communicating to everyone to stop entering in Skittles because we sold out is almost impossible. Last and final question, If data validation can tell it to stop at a specified limit, can it or another feature give us a warning message when our stock quantities are depleted?

[MarvinP]

Hi Rob,

I have some answers and some suggestions.
Q2: Validation only works on the cell you input into (I think). So you are correct that you can't use validation on a calculated cell. I like CF for that idea. You might use VBA for that too.

Suggestion.
On your invoice form you need to remove all those Merged Cells as they will hurt more than help in the long run. Rows 65,66 columns G&I are real killers.

I can imagine you using Advanced Filters on the Invoice form to get a compacted Print sheet but not with your merged cells as mentioned above.

Suggestion - You could use named ranges to do more of your work.

I understand that you can't network outside the prison to get to the WWW but I don't know how you save each invoice on a common server. There may be a way to update the 4 satelite sheets with current inventory using VBA but you don't want to lose or overwrite data.

Keep asking questions and tackle each problem at a time. Your work will get better and better.

[83Rob83]

Thank you! I took your advise and deleted every single merged cell! Also, I figured out how to alert us when our stock is getting low. Power Query adds up all the quantities onto another workbook which list all the items in Column A, how many on a case in Column B, how much each item is in Column C so on. The sold quantity that is generated with Power Query is subtracted from from what I have instock which is figured out by what my original inventory count is plus in Column E plus what I receive in In Column F. Long story short, I reference the on hand stock quantity back into the invoice in a column right next to the item then use Conditional Formating to tell it to turn that item red when the on hand is less than 1. That probably made no sense so ill include a few pictures. You'll see, once I deleted the number in Cases instock, The Item turned red, Also note the formula in the formula bar thst points to the Inventory sheet. I'm easily amused, I know..

[83Rob83]

Here are the two files I've been working on. I don't know if they'll still work together after they've been uploaded and downloaded on another computer but give them a try. Delete the numbers in the "Cases Instock" column in the Inventory file (sheets "Food Inventory" and "Non-Food Inventory") and you'll see the items on "Invoice" turn red. I'm curious to hear from a pro how I did. I've only been using Excel for a few months but I think i'm getting the hang of it. I don't think there is anything that Excel can't do! A test SBI # is 101010. Copy the "Invoice" and name it Invoice 1, Invoice 2, Invoice 3 and put it into a "Invoice3" fold on your desktop so the query works.

[MarvinP]

Hi Rob,

If you're using Power Query you are ahead of lots of folks in that tool. BUT - don't get too cocky as I've using Excel for years and only know about 90% of it. Did you protect your invoice sheet so only column A can be used? I'm afraid that the two columns on the invoice sheet will hurt you later on. Is there a way you could make it A to E and not use the G to K columns? That would cut your work in half. People would need to scroll down to see more data but it would really help calculations and ease of use. If your staff is entering the data and the mates are doing it on paper, this should be possible. Also, I'd create a column that has a "Date or Order" instead of putting it at the top. That way when you want a week or month total, it would be in a single table. Could you create a unique order id using Mates number and date/time stamp? I'm not sure when someone will question your numbers and you'll need to find that specific order. There will always be data entry error from paper to computer. This would help to see where the error was made.


Keep working with it and it will get better.