Question about Tables ?

[davidbowie]

Hi,

I am creating a spreadsheet to track customers contact information and purchase history.

A B C D
1 First Name Last Name Phone Number Etc.
2 Mike Jones
3

I wanted to know if theres a way to click on a customer (say Mike Jones) and be able to enter and view purchase history information relating to that customer in either a drop down box or different sheet?

ex.
Mike Jones
Date Item Spent Last Contacted
4/24 Toy $500 4/31
5/16 Movie $15 5/20

Any feedback would be most appreciated!!

Thank You!!

 

[Smitty]

Welcome to the Board!

You might want to look into User Forms.

Once you have the form doing what you want, you can easily summary purchases by customer with a Pivot Table.

HTH,

 

[davidbowie]

Thanks but Im still having a tough time

I want to create a drop down list that has purchase history I can add and edit when I click on a customer name. Any ideas?

 

[Smitty]

If you use a form, you can use a Combo Box filtered on the Customer Name to return their purchases. However, you then need to design the form output for either New Sale or Edit Existing. It's not all that complicated, but takes some time.

Another option would just be to use worksheets. First to enter/select customers from a validation list (if it's an existing customer then you can use dependent validation to list the purchases), then use a Change event to either add a new customer or edit an existing record on another sheet. Excel MVP Zack Barresse has a blog post on dependent data validation here.

However, if you're dealing with a lot of customers, you might want to consider using Access, especially as there are pre-built (free) templates for just this kind of thing. Just check the Microsoft Template Gallery.