Excel Inventory Log help

How to create a workbook that has a log worksheet with multiple dependent drop down lists and depending on what is selected on those lists the quantity entered on the log will then auto-populate to a different work sheet?

i.e. List1 "Guns" and "Roses". List2 "Small, Medium, Large" or "Red, White, Blue".

Then a user chooses "Guns" and "Small" and has to enter a quantity. Now I have a spreadsheet for Guns and a separate spreadsheet for Roses.

How can I have excel automatically send the new quantity to the right spreadsheet?

This is an inventory log that multiple users will have access to.

Much thanks!!