possible stupid excel question - cross referencing 2 sheets

[JayPatel]

I have 2 spreadsheets that contain usernames and sales numbers.

what I want to do is have excel lookup the exact usernames off of both spreadsheets and then have it input the persons name, and the corresponding sales values from each spreadsheet in columns next to the name.

what I am trying to accomplish is to show % +/-'s from 1 week to another based on a reps sales numbers, but I dont want to manually lookup the user and then enter the values in myself.

if excel cannot find the same name, would it also be able to indicate that in the cell as well?

 

[Mitzi]

Take a look at the VLOOKUP function.

 

[JayPatel]

im not familiar with the lookup function. can you be a little more specific?

 

[Dead3ye]

Your not going to be able to that just using functions in cells, especially if your looking at two different workbooks or even two different sheets in the same workbook.

Visual Basic is about the only way your gonna be able to to that.

 

[bunker]

This is screaming for an access database.

 

[JayPatel]

any other ideas?

 

[NeoV]

a combination of if(iserror) statements and vlookups will do the trick...

IN spreadsheet one, if you have John Doe in Row 1, and Jane Doe in row 2, and their sales numbers (in Column B)for week 1 are 10 and 15, respectively....I take it you are saying that you would then have spreadsheet 2 with more names and numbers, perhaps for a different week?..lets say John Doe in row 1 and Sue Doe in Row 2..and you have 15 and 20 for their sales in week2, in column B...and you want to know, in column C, what their % change was from week 1, right?

Here is the formula in cell C1:

=IF(ISERROR(VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$2,2,FALSE)),"New",((VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$2,2,FALSE))-B1)/B1)

IT really looks more complicated than it is.....

IF(iserror(X), true, false) = the basic setup...it says that if the vlookup, which is looking for the name in cell A1 in a defined range (your range of data in the other spreadsheet) isn't there, then put the word "New" in C1...if the vlookup does find a match, then take the value in the 2nd column to the right of that name in the other spreadhseet, and calculate the percentage change...

You can copy that forumla down for each row...

lmk if you have questions, I work with this type of stuff all day!