Excel Billing & Inventory Automation

I have to run daily medicine sales from a single Excel file and want the billing sheet to talk directly to an inventory list. Each time I enter a sale on the billing tab, the corresponding quantities in stock should drop automatically. The workbook therefore needs:

• A clean inventory table that stores medicine name, current quantity, expiry date and batch number.
• A billing sheet where I type the item code or pick from a dropdown, enter quantity sold and see price, total and balance instantly.
• Behind-the-scenes formulas or VBA that locate the right batch, subtract the quantity and leave me with updated on-hand figures without any extra clicks.

Sales inputs will be recorded only inside Excel—no external POS—so everything has to run natively in the workbook. If you prefer Power Query, structured tables or pure VBA, I am flexible as long as the flow is simple for non-technical staff.

End result: one file, ready to go, with clear cells I can edit (medicine master, prices) and protected automation I can’t accidentally break.