Formula Help...

[cb95amc]

I apologise in advance if I am not able to explain exactly what I want
to do with this formula, but here goes....

I have a formula that references 2 cells on a separate worksheet based
on the entries in a number of other cells, and then divides these two
values - The formula is shown below.

Basically it is looking up Sales data using one of the ranges, and
Stock levels using the other range in order to calculate the number of
weeks of stock cover.

=VLOOKUP($B3,INDIRECT("'"&C$2&"'!$F$300:$BF$492"),MATCH($B$1,INDIRECT("'"&C$2&"'!$F$301:$BF$301"),0),0)/(VLOOKUP($B3,INDIRECT("'"&C$2&"'!$F$1:$BF$300"),MATCH($B$1,INDIRECT("'"&C$2&"'!$F$1:$BF$1"),0),0))

$B3 is a reference to a specific product which appears on the
referenced sheet (in rows)
C$2 is the sheet name it needs to get from
$B$1 is the specific week to lookup and references the columns on each
sheet.

What I would basically like to do is be able to use a range of 4 weeks
rather than a single week, but that 4 week range needs to be dynamic,
based on the selection in B1.
For example if I select Week 10 in B1 I would like it to take the value
in Week 10 plus the 3 previous weeks on the other sheets.

I am assuming it requires the use of OFFSET or similar, but I have been
unable to figure it out.....I am still a bit of a novice when it comes
to complex formulas....

FYI - I have used this structure because I need to be able to copy this
formula to about 1000 cells.

Many thanks

Andrew

[Domenic]

For the denominator part of the formula, try...

D2:

=MATCH($B$1,INDIRECT("'"&C$2&"'!$F$1:$BF$1"),0)-1

E2:

=SUMPRODUCT((INDIRECT("'"&C$2&"'!F2:F300")=$B3)*(OFFSET(INDIRECT("'"&C$2&
"'!F2:BF300"),,$D2,,-MIN($D2,4))))

For the numerator part of the formula, change the references for each
formula accordingly.

Hope this helps!

In article <1137595638.190226.88800@g47g2000cwa.googlegroups.com>,
"cb95amc" <andrew.carroll@europe.com> wrote:

> I apologise in advance if I am not able to explain exactly what I want
> to do with this formula, but here goes....
>
> I have a formula that references 2 cells on a separate worksheet based
> on the entries in a number of other cells, and then divides these two
> values - The formula is shown below.
>
> Basically it is looking up Sales data using one of the ranges, and
> Stock levels using the other range in order to calculate the number of
> weeks of stock cover.
>
> =VLOOKUP($B3,INDIRECT("'"&C$2&"'!$F$300:$BF$492"),MATCH($B$1,INDIRECT("'"&C$2&
> "'!$F$301:$BF$301"),0),0)/(VLOOKUP($B3,INDIRECT("'"&C$2&"'!$F$1:$BF$300"),MATC
> H($B$1,INDIRECT("'"&C$2&"'!$F$1:$BF$1"),0),0))
>
> $B3 is a reference to a specific product which appears on the
> referenced sheet (in rows)
> C$2 is the sheet name it needs to get from
> $B$1 is the specific week to lookup and references the columns on each
> sheet.
>
> What I would basically like to do is be able to use a range of 4 weeks
> rather than a single week, but that 4 week range needs to be dynamic,
> based on the selection in B1.
> For example if I select Week 10 in B1 I would like it to take the value
> in Week 10 plus the 3 previous weeks on the other sheets.
>
> I am assuming it requires the use of OFFSET or similar, but I have been
> unable to figure it out.....I am still a bit of a novice when it comes
> to complex formulas....
>
> FYI - I have used this structure because I need to be able to copy this
> formula to about 1000 cells.
>
> Many thanks
>
> Andrew