I need to make a stock tracker in excel. It will be a shared document and there is going to be some not very excel proficient individuals using it. Essentially, I need to have a table which they fill out two columns: the stock (consumables) used [column a] or the stock level that day [column b], or both. If they fill out neither, then the cells are left blank.
Then, they need to press a button that executes the following process:
1. Look at today's date
2. Copy the information from consumable#1 [column a] into a master list where the daily consumption and stock level is recorded
2.1 Repeat for all 4 consumables (they each have a separate master list)
3. Copy the information from consumable#1 [column b] into the same master list
3.1 Repeat for all 4 consumables
The issue I keep running into is I only want it to update that specific day, and keep all previous records untouched. For clarification, this master list then forecasts when we will run out of stock (the daily consumption is averaged) and based on delivery times for each consumable it provides the last day we could order to get new stock in time for running out.
Any help would honestly be tremendous. I have no idea how to use VBA and god would this end an ongoing nightmare for me.
See attached screenshots for clarification.
Then, they need to press a button that executes the following process:
1. Look at today's date
2. Copy the information from consumable#1 [column a] into a master list where the daily consumption and stock level is recorded
2.1 Repeat for all 4 consumables (they each have a separate master list)
3. Copy the information from consumable#1 [column b] into the same master list
3.1 Repeat for all 4 consumables
The issue I keep running into is I only want it to update that specific day, and keep all previous records untouched. For clarification, this master list then forecasts when we will run out of stock (the daily consumption is averaged) and based on delivery times for each consumable it provides the last day we could order to get new stock in time for running out.
Any help would honestly be tremendous. I have no idea how to use VBA and god would this end an ongoing nightmare for me.
See attached screenshots for clarification.