Hi Joel,

I Entered the formula below however, I receive an error message
SUM(OFFSET((MATCH(D8,'Previous YR'!A1:A1650,0)),0,10,1,COUNTA(X8:BR8)))

Thanks for any help

Ramone

"Joel" wrote:

Use match instead of vlookup

"ram" wrote:

I'm Trying to use vlookup for the reference in the offset function, however I
don't know how to change the value from the vlookup into a cell refrence for
the offset.
I tried cell("address", vlookup(....). I also tried indirect
(vlookup(....),a1).

Details:

Sheet1 contains agent ID#'s and sales for each week in 2007
Sheet 2 contains the same information for all of 2006

On sheet1 I have a column the gives the total forthe year
I also have a column that needs to provide the corresponding YTD for 2006

The sum and offset function works for one agent but it won't look up all
the agents.

Thanks for any help

Ramone