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

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

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

Match return the offset in column A the equal the item in D8. Offset will
find go to the value that match found and get the number of item between
X8:BR8. then sum will gett the total of these items


=SUM(OFFSET('Previous YR'!A1,0,(MATCH(D8,'Previous
YR'!A1:A1650,0)),1,COUNTA(X8:BR8)))


"ram" wrote:

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

Thanks for the help Joel
I got it to work

"Joel" wrote:

Match return the offset in column A the equal the item in D8. Offset will
find go to the value that match found and get the number of item between
X8:BR8. then sum will gett the total of these items


=SUM(OFFSET('Previous YR'!A1,0,(MATCH(D8,'Previous
YR'!A1:A1650,0)),1,COUNTA(X8:BR8)))


"ram" wrote:

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