Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Greetings,
I have a customer list of which 70% have only one entry per customer. The problem is the oddballs: Customers who change their names (get married) Customers who change their address Customers with more then one service address (Real estate agents) Customers who have more then one phone number or change their phone numbers And customer who nave many of these exceptions I would like to have one record per customer. What I have is a separate record for any changes that occur. Is there anyway to redesign this customer list to limit it to one record per customer? Any suggestions would be most appreciated TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Hello Minitman, Add one field for each exception to be included in the record. Expanding the record's number of fields should be easy enough to do. Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=480496 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Hey Leith,
Thanks for the reply. I am a bit confused, You are using database terminology to apply to a workbook. I could not get Access to work so I am still in Excel. The way the customer list is now set up is thus: | Name | Home Phone | Office Phone | Cell Phone | Emergency Phone | Billing Address 1 | Billing Address 2 | Service Address 1 |Service Address 2 | Map Code Most of the customers will fit into this template. It's the ones that have active information that I am trying to deal with. With over 2000 customers I need to stream line the search for a particular customer in my entry and invoicing workbook. As it is, 90% of the customer are no problem. But for example, I have 5 real estate people each with 20 properties that they will send us to. Do I set up 5 customers with multiple service address or do I set up 100 different records in my customer listing. I thought of using drop validation list for carious service addresses, but when I finally do transport this system to Access I'm not sure if that will work (and sure I don't know HOW to make it work) It appears that you may have some idea as to how to do this, Could you share some examples? It will be of great help. Thanks -Minitman On Sun, 30 Oct 2005 23:32:04 -0600, Leith Ross wrote: Hello Minitman, Add one field for each exception to be included in the record. Expanding the record's number of fields should be easy enough to do. Sincerely, Leith Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Hello Minitman, When a worksheet is set up with Column and/or Row headers, it is in fact a database, often referred to as a list. A Row represents a record and the Columns the fields. You have 256 Columns (fields) per record and 65536 records per worksheet. So, you have plenty of space to work in. This layout can be easily imported to an Access database, when the time comes. Excel provides a builtin tool to help you manage lists like yours. Its the Data Form. To use it, your list must have column headers (and you do). On the main Excel Toolbar, click Data and in the list click Form. You will see a window that controls to move through and modify your data. There is a limit to the number of columns it can handle, but I am not certain what that limit is. I don't think you will exceed it with your list. For best results, you should alphabetize your list by name. You can do this manually with Excel or add a command button to automate the process. Back to your question. Add the additional fields for those special customers. Then alphabetize your list by contact name. Then it's an easy matter to scan the properties associated with that customer using the builtin data form. Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=480496 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Hey Leith,
I have a question about the advisability of adding 20-50 new columns just for 5 customers out of 2000? It seems like such a waste. I was considering adding a validation drop down menu to the 4 fields(columns) that have additions (each row will be directed to customer specific sub lists'). I guess I'm looking for a philosophical discussion as to the merits of the various options. Looking forward to hearing from you. -Minitman On Mon, 31 Oct 2005 11:19:45 -0600, Leith Ross wrote: Hello Minitman, When a worksheet is set up with Column and/or Row headers, it is in fact a database, often referred to as a list. A Row represents a record and the Columns the fields. You have 256 Columns (fields) per record and 65536 records per worksheet. So, you have plenty of space to work in. This layout can be easily imported to an Access database, when the time comes. Excel provides a builtin tool to help you manage lists like yours. Its the Data Form. To use it, your list must have column headers (and you do). On the main Excel Toolbar, click Data and in the list click Form. You will see a window that controls to move through and modify your data. There is a limit to the number of columns it can handle, but I am not certain what that limit is. I don't think you will exceed it with your list. For best results, you should alphabetize your list by name. You can do this manually with Excel or add a command button to automate the process. Back to your question. Add the additional fields for those special customers. Then alphabetize your list by contact name. Then it's an easy matter to scan the properties associated with that customer using the builtin data form. Sincerely, Leith Ross |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Make A Customer List With Extra Entries Per Customer
Hello Minitman, Rather than create a Drop Down and the code to use it, why not use second sheet in your workbook for the specail cases. You can still us the Data Form and make your transition to Access easier down the road Unless of course, you really want that Drop Down list! Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48049 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
customer Name against Unique customer number count | Excel Worksheet Functions | |||
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET | Excel Discussion (Misc queries) | |||
How to make customer database with search engine by addy or name | Excel Discussion (Misc queries) | |||
Can I make a customer list that converts to labels? | Excel Discussion (Misc queries) | |||
How can I find and mark double entries in a customer database? | Excel Worksheet Functions |