what function/formula do I use?

[kate]

I have one sheet in my workbook with raw data entered daily. This
information includes employee number, and scores. I want to pull scores from
that sheet to a different sheet based on the employee number in order to
calculate an average score. This should be easy, but I'm stumped. I'm also
haven't used Excel in a long time.

Currently using EXCEL 2007, but may need backward compatibilty to 2003.

Any help is greatly appreciated.

Kate

 

[Roger Govier]

Hi Kate

Assuming employee numbers are unique, and entered in column A of Sheet1,
with scores in columns B through M
On Sheet2 in cell B2
=IF($A2="","",VLOOKUP($A2,Sheet1!$A:$M,COLUMN(),0))
You can then drag this formula across through C2:M2, and then down as far as
required.

Change the ranges to suit your situation.

 

[kate]

Roger,

Thanks for your input. I think we're on the right track, but I may not have
explained it too well.

RAW DATA (sheet 1)

Col A Col F

tech number score



TECH AVG (sheet 2)

Col A Col B Col C etc
6801 6802 6803

score score score
score score score
score score score

avg avg avg


There will be multiple entries (rows) with the same tech number and a score
for each entry. Tech numbers will not be in order. The number of entries
per tech will vary.

I just want to pull all of (ex. tech 6801) scores to a new sheet, add them
up and average them. I can handle the averaging.

Thanks for helping a neophyte.

Kate