Customer id # & state abbreviations

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.

Here are the photos of the documents, the first is the Customer contact and the 2nd is the rag rugs. I had a problem loading them with the original post.

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

If the file is too big, reduce the amount of info in it. All we need is a small trepresentative sample of what you are working with, just enough to show what you have, and what you want.

edit: Its OK, I copied all your files into 1 file

For problem 1...the ID
=A2&"-"&E2&"- "&UPPER(LEFT(D2,2))&LEFT(C2,1)&"-"&TEXT(G2,"mmyyyy")

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.

I have created a dynamic name range, and then used data validation on Rugs, to give you a DropDown for the customer ID's. Click the DD, select an ID, anfd you are done - saves trying to remember the ID, and saves on making typos

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.

I used a serires if INDEX/MATCH to pull this in for you

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.

Not sure why you need this? My formulas pull in the full state name.

take a look at the attached and see if we are heading in the right direction here?

that looks good so far, but the reason why i wanted to type in the abbreviation and receive back the state name is because the zip codes and cities in the "shipping" document are not connected to the states themselves. take for example, if you type in the zip code 44420 in the rag rug cell AV7 it will return a value of 0 in cell AU7.

i could provide an additional column linking each zip code with it's corresponding state. It's probably impossible to link the city name to the state as many states have duplicate city names.

I went ahead and created a column in "Shipping information" or "Shipping" for you, where each zip code was represented by the state abbreviation. From there i made a 2nd column converting each abbreviation to the state name. Then back in the "Rag rugs" document i indexed and matched the state column name to the zip code, thus solving the problem.

I downloaded a new set of zip codes for all the cities including their states from here...
http://federalgovernmentzipcodes.us/

I have adjusted the formulas to read from this new table, so you can delete the other 1 if you want.

because the file grew beyond the limits of the forum, I have saved it as .xlsB. You should have no problem opening this format, you can either leave it as this, or Save As back to .xlsX format

Happy to help, thanks for the feedback