Hi there,

I am looking for a simple excel solution to a relative simple application (i think).

Scenario: My wife attends craft weekends and runs "tabs" for customers in excel. At the end of the weekend she has to cash everyone out. Currently she takes a name, and some other info, then start populating rows underneath with what they purchase.

Problem: The problem is when they check out it is difficult to search through 100 names on one sheet when all the sale items are intermingled between names, then as people pay it gets real messy having to visually differentiate payed and non-payed customers in the single sheet.

Question: Is there a way to have the first sheet with just first names and last names so they can be sorted and then have each name link to another location where the rows sale item information and totals are?
She will need to go back and forth between these two locations often until the customer finally closes out the sales.

Thanks for your help!

Dave

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.

Attached Files

sample.xlsx (14.4 KB, 18 views)