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?