I have a sheet called "Master Client List" that contains 4 columns of data: Counselor Name, Client Number, Client Last Name, Client First Name. Row 1 on this sheet is a header that consists of these 4. This is sorted by client number and all of the counselor's names are mixed up. I'd like to make a separate sheet for each counselor that just contains their clients while maintaining the master sheet.
Let's say sheet 2 is dedicated to the counselor named Darth Vader. I'd like to go to sheet 2, which would be dedicated to him and would only house his clients. Using the same header from sheet 1 (Master Client List), I'd like to be able to enter "Darth Vader" somewhere on sheet 2 and then have it automatically pull all of the clients from sheet 1 that have Darth Vader listed as the counselor over to sheet 2.
Then I'd like to do that with the rest of the counselor's in the office as well on sheets 3, 4, etc.
You are trying to use a spreadsheet as a database. A spreadsheet is for calculating, not for storing.
Start a new Access db, and create some tables:
COUNSLER -this is where all of the counselor stuff is stored, that is counselor specific (phone, address, office, NO CUSTOMER DATA). Make certain you give each counselor a unique number.
CLIENTS - This is the clients sheet. Make certain you give all clients a unique number.
Appointments - This is where you put the date and time of the appointment, and the unique counselor number, and the unique client number, and maybe a short description of what happened.
Now you can link it all together, by reporting on the appointments table, linking the appointments to the clients and the counselors!
It sounds like alot of work, but in the long run, it scales. EXCEL solutions do not scale, and if we make an excel solution, you will continually have to alter the formulas as your list grows!
Let me know if you need any help.
It's kind of fun to do the impossible! -Walter Elias Disney
