Referencing other Sheets, how do a make a value absolute if the referencing worksheet changes?

[Bschwa]

Confusing title? Yea, I think so to, especially since I'm not really sure what the right terminology for what I'd like to accomplish is.



I'm putting together a spreadsheet to track all my sales reps contacts (we're a small startup and don't have money for things like Salesforce, which would make like easier). We have a predefined worksheet we send to our reps and when they make contact and secure a meeting, they fill out the form, send it back to HQ and we assign a numerical value to their contact. This way, when a sale is made down the line and sent to us via fax (direct from the client), we know that Contact 1 belongs to Sales Rep 1 and they get the commission for the sale.



With several reps out in the field making contacts, they are constantly sending us their list. So, I've got the master with me and I have one worksheet that will import several key details that we'll use to quickly match the client with sales rep. The second worksheet is the clients demo. details (company name, address, contact info, etc) that we'll use for more detailed information. The third worksheet will match what the sales rep provides to us.



Now, my issue is the third worksheet, it's constantly changing. I want to figure out how to reference the third spreadsheet to import the client information into my other worksheets (through Defined Names, perhaps) but then convert them into absolute(?) values. So once it's entered and the third spreadsheet is updated for a new list of contacts, that reference does not change.



Example -

Worksheet Three (Rep's Information)

Cell A5 - Jones, Inc (Defined Name is PracticeName)

Worksheet One (Contact List):

Cell A4 - Will need to equal the cell A5 on worksheet three



So, it's pretty simple to have those two cells equal/reference each other, however once I import (copy/paste, whatever) new sales contact info into the third sheet, the data in the first worksheet will update with the new data. I don't want that. I want it stay as is and then the next row I can update with new client contact details and keep a running list of all the efforts my sales teams has made.



I hope this request makes sense and I appreciate you all bearing with me as I better understand what I need to get done. Please ask me questions for further clarification.

 

[RickXL]

Hi and welcome to MrExcel,

If it was me I would design it like a database. So I would have a worksheet with a complete list of sales people. Each person should be given a unique ID. For ease in Excel, the ID should be the left-most column. Then there should be another worksheet with all the clients. Again each client will be given a unique ID. This will help out when you get two clients both called J Smith or when you have different sales people dealing with different parts of the same large organisation.

The client worksheet will need to have a column for the ID of the sales person. This will be the list that relates clients to sales people.

Now you should be able to add any data you like to a worksheet by using VLOOKUP on the various IDs.

For instance, when you get an order, the order details will have the client IT on it. You will be able to get all the other client details from the client worksheet. (Having more than one name for a client can be a problem - this should eliminate that possibility.) The sales person's ID will be on the client worksheet so you can now add the sales person's details to the order, if necessary.