hi guys. I've been on here before in my attempt to create a database for my mother's new business. Things are getting pretty complex and i need some advice and or answers to continue the process. I hope some of the experts on here can help me out, it's very much appreciated!!
The first sheet is called "Customer Contact"
The second sheet is called "Rag Rugs"
The third sheet is called "Shipping information"
Problem #1:
When a new customer makes a purchase i want to be able to reference that customer in the future by a specific ID number. I'd like the ID # to be 16 digits long and comprise values from different cells. I decided to make a "CUSTOMER CONTACT" sheet to keep track of the individual customers. In "CUSTOMER CONTACT" CELL B2 i want the number to reflect CELL A2, E2, D2 (only the first two initials), C2 (only the first initial), and G2 (only the month and the year).
The final number should look exactly like this (including the dashes): CC-00001-JOD-052015.
Problem #2:
In the sheet titled "RAG RUGS", i want to type the customer's ID number in CELL AO6 and have excel insert the customers first and last name in the corresponding cells AP6 & AQ6.
Problem #3:
In the document titled "SHIPPING INFORMATION" I have made columns that house state names and abbreviations, along with cities and their zip codes. When I'm filling out the address of the customer in both documents, "RAG RUGS" and "CUSTOMER CONTACT" i want to be able to type in the zip code and excel fill out the city column for me. Example: In "CUSTOMER CONTACT" i want to type in the zip code in Cell H2 and i want Excel to return "Turtle Lake" in CELL J6. Zip codes are in "SHIPPING INFORMATION" CELL U3:U58575 and the corresponding cities are in CELL V3:V58575.
Problem #4:
Finally I want to type the state abbreviation in "CUSTOMER CONTACT" CELL K2, and "RAG RUGS" CELL AU6 and have excel return the entire state name. State names are in "SHIPPING INFORMATION" CELL Q3:Q61, and the state abbreviations are in "SHIPPING INFORMATION" CELL R3:R61.
Obviously i would like to drag the formulas to the next sequential cell if possible.
Thank you to everyone who takes some time out of their busy lives to help me out, i can't thank you enough! I've attached some pictures and the workbook below. Thank you again,
Rachel
RAG RUGS.jpg
CUSTOMER CONTACT.jpg
**For some reason when trying to attach the workbook the uploading process times out, so it seems i can't attach the combined document to this post, so i separated the document into 3 workbooks, Shipping information, customer contact, and rag rugs.
Bookmarks