Previously... I had in issue which was resolved by using the following.

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Thankyou (Biff)


My new problem is similar except that my forecast is on a seperate sheet
and I need to use a match/vlookup? formula to get the row numbers of where
the usage amounts are.

Sheetname = Data.

Dates in the range - Data!$G$1:$AA$1 (Constant)

Usage amounts in the range Data!$G$x:$AA$x
(where "x" = the row(s) number.)

I don't know the row number unless I use Match.
Eg: =MATCH(VALUE(A4),Data!A:A,0) - Which Returns "16" - Which is the
correct row number.

Is there a way of incorporating the Match formula into thearray??

Note: The Usage amount are spread over 2 rows.
Eg: Data!$G$2:$AA$3

Any help would be much appreciated.

Yes, use INDIRECT.

INDIRECT("Data!$G$"&MATCH(VALUE(A4),Data!A:A,0)&": $AA
$"&MATCH(VALUE(A4),Data!A:A,0))

Try inserting x-2 into the row offset part of Biff's formula where x is the
result of your match formula.

To make it non-volatile, you could try replacing subtotal(...) by:
prob(D1:J1*{1;-1},index(D:J,x,)*{1;-1}+(D1:J1=D1)*{0;1},,D1:J1)

"Bam" wrote:

Previously... I had in issue which was resolved by using the following.

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Thankyou (Biff)


My new problem is similar except that my forecast is on a seperate sheet
and I need to use a match/vlookup? formula to get the row numbers of where
the usage amounts are.

Sheetname = Data.

Dates in the range - Data!$G$1:$AA$1 (Constant)

Usage amounts in the range Data!$G$x:$AA$x
(where "x" = the row(s) number.)

I don't know the row number unless I use Match.
Eg: =MATCH(VALUE(A4),Data!A:A,0) - Which Returns "16" - Which is the
correct row number.

Is there a way of incorporating the Match formula into thearray??

Note: The Usage amount are spread over 2 rows.
Eg: Data!$G$2:$AA$3

Any help would be much appreciated.

Lori, I'm not to sure what you mean?

Can you put it into the formula for me to test?

Thanks for your help.

"Lori" wrote:

Try inserting x-2 into the row offset part of Biff's formula where x is the
result of your match formula.

To make it non-volatile, you could try replacing subtotal(...) by:
prob(D1:J1*{1;-1},index(D:J,x,)*{1;-1}+(D1:J1=D1)*{0;1},,D1:J1)

"Bam" wrote:

Previously... I had in issue which was resolved by using the following.

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Thankyou (Biff)


My new problem is similar except that my forecast is on a seperate sheet
and I need to use a match/vlookup? formula to get the row numbers of where
the usage amounts are.

Sheetname = Data.

Dates in the range - Data!$G$1:$AA$1 (Constant)

Usage amounts in the range Data!$G$x:$AA$x
(where "x" = the row(s) number.)

I don't know the row number unless I use Match.
Eg: =MATCH(VALUE(A4),Data!A:A,0) - Which Returns "16" - Which is the
correct row number.

Is there a way of incorporating the Match formula into thearray??

Note: The Usage amount are spread over 2 rows.
Eg: Data!$G$2:$AA$3

Any help would be much appreciated.