I have been requested to create an inventory Excel Sheet. I need to track Monitors, Mouse, Keyboard, Cable. Each will begin with a fix number (example 10, 20, 35, 15). I created a pulldown for each type of device (list name DEVICE) and another pulldown with 3 choices (Add, Borrow, Returned – list name STATUS).
If a device (DEVICE pulldown) is borrowed (STATUS pulldown), depending on the cell value (example 4 in E5), I want that cell number value to be subtracted from that device total.
If a specific device is Add/Returned, depending on the cell value, I want the cell number value to be increased to that device total.
Example: We begin with 10 monitors. 4 people borrow monitors, so the total is now 6. A few days later 1 person RETURNs a monitor, the total should now be 7. A week later we purchase (ADD) 2 more monitors, so the new total would be 9. A month later the remaining 3 monitors are RETURNed – so the new grand total should be 12.
So I am guessing I need a formula to reflect the device chosen compared to status and the number in a specified cell to reflect the total number.
I hope this makes sense.
I know some basic Excel formulas (Sum=B1-B2), but since this has many variables I am unsure what type of statement/formula to use. Since this will be a living document, I need it to be consistent over multiple lines.
Any help would be greatly appreciated.
I thank you in advance.
