I am having trouble creating a formula to sum up numbers on a separate worksheet based on the values in my first sheet. The problem I am having is that sheet1 is based in months, but I need weekly values, which I have in sheet2. Sometimes I need the sum of values from 3 or 5 weeks worth of data, which isn't compatible with the monthly numbers. I am very familiar with vlookups, but I am unsure of how to use the offset formula (if that is the simplest way) in this situation.
I have tried to use =sum(offset(vlookup(A1,'sheet2'A:F,2,false),0,4,,4)) but it doesn't seem to work.
Here is a simplified version of some data I am using:
Sheet 1
Item# Weeks
782 4
609 5
936 3
Sheet 2
Item # Week1 Week2 Week3 Week4 Week5
782 2 4 5 3 2
609 5 3 6 2 4
936 2 3 5 4 1
So, for item 782 I need to add up the first 4 weeks, for 609 I need the first 5, and for 936 I need the first 3.
I would really appreciate any suggestions and tips. Thanks
I have tried to use =sum(offset(vlookup(A1,'sheet2'A:F,2,false),0,4,,4)) but it doesn't seem to work.
Here is a simplified version of some data I am using:
Sheet 1
Item# Weeks
782 4
609 5
936 3
Sheet 2
Item # Week1 Week2 Week3 Week4 Week5
782 2 4 5 3 2
609 5 3 6 2 4
936 2 3 5 4 1
So, for item 782 I need to add up the first 4 weeks, for 609 I need the first 5, and for 936 I need the first 3.
I would really appreciate any suggestions and tips. Thanks