Create address book in 1 workbook to use in another workbook

[NA5KAR]

Good Morning All,

Thank you in advance for any help provided. I hope that I complied with Rule 1 in my title. I have a well functioning workbook for my business. I have a Drop Downs page in that workbook and the other pages look to that page for the drop down lists. It works very well. I am using Excel 2003. I am self taught (with your help) and still learning.

I want to create a separate workbook to keep all of my client names and addresses in. Right now, that info is on the Drop Downs page in my original workbook. By having a second workbook, I can more easliy open the new workbook and update the data with new clients. Here is what I want it to do:

Can the original workbook look to the 'new workbook' for the client name and address data and use it as a 'drop down' in the way I use it now? I have never used macros (don"t know how) and although I have heard of a Pivot Table, I don't know what it is or how to use it. Thank you again.

Dan

[MarvinP]

Hi NA5CAR,

If you put all your dropdown lists in a separate workbook you will still need to have it opened in excel to work with it. I believe it will be much slower also to draw info from a second workbook. You may be using Excel correctly by having your dropdown lists on a different sheet in the same workbook.

I'm not sure how you would use a pivot table. Could you discribe what you are trying to accompish or the problem you are running into for further advice.

[NA5KAR]

Thank you for your response. What I have currently is a multi page workbook. My business is screen printed apparel and promotional products. The first page is just a 'Data Sheet'. I enter client info and details about the job. Some of that info passes on to other pages in the workbook like the 'Client Invoice' and 'Purchase Order'. The last page in the workbook is labled 'Drop Downs'. I have 20 or more drop down lists on that page. The other pages in the workbook draw from that page. It works very well.

Currently, whenever I acquire a new client, I need to open my "Whole Job Docs" workbook and navigate to the 'Drop Downs' page. Then I need to scroll over to find the fields with the client list in it. I enter the new client info and save the workbook. This works 'ok' but I would like to be more efficient. My goal is to have a dedicated workbook that only has 'Client Info' in it. Then, when I open my 'Whole Job Docs' workbook, it would look for the 'Client Info' workbook and grab the 'database' of client info for the drop down areas of the original workbook. Whenever I acquire a new client, I could enter their info in the 'Client Info' workbook.

I only mentioned Pivot Tables because when I use Help in Excel, that name shows up a lot. Sorry for the long winded explanation. Thanks again.

[MarvinP]

Hi Na5KAR,

It looks like you have a system pretty well established. There may be two things that might help the system.

The first is using Named Ranges and better Dynamic Named Ranges. For example: if you add a customer name on your Drop Downs sheet you may need to reset the range of the dropdown cells on another sheet to include this new list. If you use a named range in your dropdown validation table AND the range gets larger based on how many names are in it, you save time and effort. Read about them at:
http://www.ozgrid.com/Excel/DynamicRanges.htm

A second possibility would be to build you lookup tables based on all the jobs you've already done. If your jobs data is in a List you could create a pivot table of a singel item on your Drop Downs Tab. The pivot table would then show a single occurance of that item and sort them also. This might help you in building the dropdown lists.

I hope this helps. To work on it more, I'd need some sample date with tab and tables.

Also - I'm sure you are using vlookup formulas to fill in your quotes data and invoice sheets. If not there is a lot that can be done to help.

[NA5KAR]

Marvin, Thank you for your suggestions. Sorry for the delay in responding. Been away from the computer for a couple of days. It sounds like the system I have in place works well enough that trying to reinvent the wheel is not necessary here. Thanks again for the advice.