This is a classic one-to-many which would be easily handled in Access. But there are plenty of ways to skin that cat in Excel.
Attached is one way. It uses Excels advanced table formatting functionality. The first tab has customer info: name, address, account balance, etc. The second has item info: description, price, etc.
The third has sales. Customer name is populated when the customer number is entered. Price is populated when item number is entered. Total is calculated when quantity is entered. Now you can filter this list by any of the fields using the little upside down triangle thingee to the right of each column header. For instance, you can just select Bill Cook, not paid, etc.
This is just one idea of the top of my head. Alternatively, customer name on the Sales tab can be populated using a drop down, but this would require that each customer have a unique name. Same with item number. And there are myriad other ways to use data validation, but this is just a rough up.
|