references

[Liz]

Hi there, and thanks for any advice...
(Excel 2002)

I want to be able to set up a spreadsheet that will look up values based on
another cell's value. I know there is more than one way to accomplish what
I'm trying to do, but I'm stuck.

Sheet 1
A B
EMPLOYEE COST
john $1.00 (cell named JOHN)
jane $2.00 (cell named JANE)

Sheet 2
A B
EMPLOYEE COST
JOHN
JOHN
JANE
JOHN

I want to set up column B in sheet 2 to look up the value from sheet 1. My
idea was to use the cell names to create a formula, but I'm open to
suggestions. A bonus would be some conditional formatting on one of the
cells on Sheet 2 if the employee does not exist on Sheet 1.

I'm setting up this sheet as a template for novice users so the cleaner the
formulas, the better! (they have to be able to add/change parts of both
sheets so protecting cells is out)

Thank you

 

[Guest]

If I undersatnd what you want try putting this formula in col B of sheet 2
and copying it down.

=VLOOKUP(A2,Sheet1!$A$2:$B$3,2,FALSE)

It looks at A2 of sheet 2 to get the name, it then goes to sheet 1 and looks
at your data to find that name, it then returns the result from column 2
(your dollar amount), the FALSE part tells it to look for an exact name match.