Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
references
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
references
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. "Liz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-increment data source cell references when copying charts | Excel Discussion (Misc queries) | |||
How to auto-increment data source cell references when copying cha | Charts and Charting in Excel | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Cell Reference's when Pasting | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) |