referencing from several sheets

[peterwob]

hi everyone, i've got a small problem that i hope you can help me with.

in sheet1, i've got a column containing the names of people (about 300 of them) who have received bonuses from an incentive program, as well as columns labeled with years. sheet2, sheet3 and sheet4 each correspond to a year, and again list the names of the people in the program, along with what they got - if anything - that particular year. some will have received bonuses each year, some every other year and some not at all.

what i want to do is to create a formula that searches in each of sheets 2, 3 and 4 for what each person received that particular year, and returns that value to its correct place (correct person, correct year) in sheet1.


i'd be eternally grateful for help with this, doing it manually is driving me crazy, and my attempt with vlookup had severely limited success.

thanks in advance
peter

 

[texasalynn]

Welcome to MrExcel board...

Post a small sample of your data layout for the summary sheet and from one of the other sheets (Sheet2,3,4), just pick one. That way it will be easier to give you a solution matching your data.

 

[texasalynn]

Excel Workbook
	A	B
5	Employee	Bonus
6	Tom	5000
7	Mary	3750
8	Carol	6000
9	Bob	2500
10
The ShtLst is a named range that houses the names of the sheets Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Excel Workbook
Name	Refers To
ShtList	=Sheet1!$A$1:$A$3
Workbook Defined Names

 

[peterwob]

Ah, sweet, thanks alot, got it to work!