MarioSpeedwagon
New Member
- Joined
- Feb 18, 2015
- Messages
- 1
Good Morning! I've been looking on this website sporadically for the past few years, but the past week has been bordering on obsession. I've seen multiple near-hits on the information I'm looking for, most of it coming from Big C, but I haven't been able to get anything to work the way I want. I'll give some brief info and TRY to clearly explain what I'm trying to do. Quick summary at the bottom.
Excel: Mainly 2013, but I would like to be able to use this spreadsheet on other machines, some going back to Excel 2003. Definitely not a priority since I think some macros/VBscripts are not compatible.
Windows 7
Skip past to get to the request
Personal knowledge of excel - I can probably use the program better than your grandfather, but that's as much as I'd brag about. I've never used a macro or successfully gotten a VBscript to work, but I just started attempting that a week ago. I've made inventory spreadsheets in the past that were very inefficiently put together and used order times, delivery times, peak output, etc to predict when something would run out, often times +/-2days and 8 months out. This took a LOT of fumbling around in the program and doing monthly inventories like a hawk to piece together a Frankenstein spreadsheet, but I would love to learn the right way (or a better way) to do it. I won't have an open semester until next year and plan on taking an excel class then, but I'm hoping one of you wizards are feeling generous in the meantime. I would much rather learn some tricks than have it done for me, but if that's asking too much, I'll happily take some help with one request in particular.
I like inventories to be easily laid out and accurate to prevent surprises. Since I'm dealing with very expensive medical supplies and medications with expiration *dates, Even more so now. What I've made (and remade) is just an overall inventory of all of our medical supplies on the ambulances and fire trucks. The workbook I have now has 3 sheets. Sheet 1 ("Inventory") has ALL of my information on it. Name, brand name, supplier, supplier product #, shift inventory, etc. I keep all of the columns except inventory count and baselines hidden and just call on them from other sheets. Eventually I'd like to get prices and expiration dates in there, but since those need to be adjusted constantly, I'm not worried about it yet. I've tried having a 4th sheet set up with data tables to draw from, but I spent about 8 hours on that without much progress...though Big C's posts taught me a lot.
Sheet 2 ("Approval") and Sheet 3 ("Order") is where I'm having trouble doing what I want. "Inventory" has a column that totals the % of stock to maintain that we have on hand. What I want to do is have any complete row (or preferably specific columns within each row) that is below a certain % of stock be copied from Sheet1 to Sheet2, not copying the product that is at a reasonable stock level.
On Sheet 2, I'd like to have that list present with the amount that I'm short by (ex: Dextrose50% -4), a cell to enter the amount that I actually want to order, and finally some sort of trigger/conditional formatting that will CUT the row from Sheet2 and List it out on Sheet3, with the Supplier and order number.
I'm not sure if I'm giving waaay too much information for a simple task, but many of the posts I've read have requests for more information shortly after. I feel like I've read most existing threads that are asking for similiar things, but I think I'm lacking the know-how to modify the codes to my needs, or even implement them. Any help, tips and especially patience are greatly appreciated. I'm hoping that I can make a spreadsheet that will be easy for someone else to walk into if were to die tomorrow.
Summary / tl:dr:
Inventory workbook with 3 sheets.
Sheet1("Inventory") - ALL Information, Periodic inventories with new columns being inserted to the left of old one.
Sheet2("Approval") - List of items that need to be ordered, based off of Sheet1's information on The item, required stock and current inventory. I'd like this list to only be populated with items that need to be ordered, and have a checkbox or trigger that approves them for order, and cuts them from sheet2, moving to sheet 3. unordered items remain.
Sheet3("Order") Basically creates an email-ready list that I can send to our various suppliers-of-the-week, sorted by the specific supplier. Items remain on chronological order as a purchase/order history.
Hopefully these images work...
Excel: Mainly 2013, but I would like to be able to use this spreadsheet on other machines, some going back to Excel 2003. Definitely not a priority since I think some macros/VBscripts are not compatible.
Windows 7
Skip past to get to the request
Personal knowledge of excel - I can probably use the program better than your grandfather, but that's as much as I'd brag about. I've never used a macro or successfully gotten a VBscript to work, but I just started attempting that a week ago. I've made inventory spreadsheets in the past that were very inefficiently put together and used order times, delivery times, peak output, etc to predict when something would run out, often times +/-2days and 8 months out. This took a LOT of fumbling around in the program and doing monthly inventories like a hawk to piece together a Frankenstein spreadsheet, but I would love to learn the right way (or a better way) to do it. I won't have an open semester until next year and plan on taking an excel class then, but I'm hoping one of you wizards are feeling generous in the meantime. I would much rather learn some tricks than have it done for me, but if that's asking too much, I'll happily take some help with one request in particular.
I like inventories to be easily laid out and accurate to prevent surprises. Since I'm dealing with very expensive medical supplies and medications with expiration *dates, Even more so now. What I've made (and remade) is just an overall inventory of all of our medical supplies on the ambulances and fire trucks. The workbook I have now has 3 sheets. Sheet 1 ("Inventory") has ALL of my information on it. Name, brand name, supplier, supplier product #, shift inventory, etc. I keep all of the columns except inventory count and baselines hidden and just call on them from other sheets. Eventually I'd like to get prices and expiration dates in there, but since those need to be adjusted constantly, I'm not worried about it yet. I've tried having a 4th sheet set up with data tables to draw from, but I spent about 8 hours on that without much progress...though Big C's posts taught me a lot.
Sheet 2 ("Approval") and Sheet 3 ("Order") is where I'm having trouble doing what I want. "Inventory" has a column that totals the % of stock to maintain that we have on hand. What I want to do is have any complete row (or preferably specific columns within each row) that is below a certain % of stock be copied from Sheet1 to Sheet2, not copying the product that is at a reasonable stock level.
On Sheet 2, I'd like to have that list present with the amount that I'm short by (ex: Dextrose50% -4), a cell to enter the amount that I actually want to order, and finally some sort of trigger/conditional formatting that will CUT the row from Sheet2 and List it out on Sheet3, with the Supplier and order number.
I'm not sure if I'm giving waaay too much information for a simple task, but many of the posts I've read have requests for more information shortly after. I feel like I've read most existing threads that are asking for similiar things, but I think I'm lacking the know-how to modify the codes to my needs, or even implement them. Any help, tips and especially patience are greatly appreciated. I'm hoping that I can make a spreadsheet that will be easy for someone else to walk into if were to die tomorrow.
Summary / tl:dr:
Inventory workbook with 3 sheets.
Sheet1("Inventory") - ALL Information, Periodic inventories with new columns being inserted to the left of old one.
Sheet2("Approval") - List of items that need to be ordered, based off of Sheet1's information on The item, required stock and current inventory. I'd like this list to only be populated with items that need to be ordered, and have a checkbox or trigger that approves them for order, and cuts them from sheet2, moving to sheet 3. unordered items remain.
Sheet3("Order") Basically creates an email-ready list that I can send to our various suppliers-of-the-week, sorted by the specific supplier. Items remain on chronological order as a purchase/order history.
Hopefully these images work...
