Indirect function in Forecast function

[Graham Tritton]

I'm trying to create 2 array references that will be used in a forecast
function for the known x's and known y values using an indirect function. one
is to reference another sheet (Known Y's) and the same sheet (known X's).
This is all encased in an if function to display a value only when the column
(week) is later then the current performance period.

The formula that I'm currently at is
"=IF(AQ$3>Last_valid_Week,FORECAST(AQ$3,INDIRECT("CSS!RC11:RC"&MATCH(Last_valid_Week,Weeks0708,)+COLUMN($K$3)-1,0),INDIRECT("r3c11:R3C"&MATCH(Last_valid_Week,Weeks0708,)+COLUMN($K$3)-1,0)),NA())"

The second indirect appears to be working as if I wrap this with a SUM
function it gives me a correct sum value, but the first does gives me a sum
of 0 which is incorrect.

The 2nd indirect resolves to "R3C11:R3C42" and each cell in this reference
contains values as does "CSS!RC11:RC42 (Which is in row 4)

Can anyone help me, I think that I will need to stay with an indirect as the
colmns of data to use will extend another column every week. - Cheers

 

[Roger Govier]

Hi Graham

Not sure that I am understanding your problem correctly, but maybe if you
play about with the following suggestion you will get the result you want.

Firstly, since $K$3 is fixed in the formula, then COLUMN($K$3)-1 will always
return 10.
I would set up another named formula as UseCol
Insert>name>Define Name UseCol Refers to
MATCH(Last_Valid_Week,Weeks0708,0)+10

=IF($AQ$3>last_valid_week,FORECAST($AQ$3,Sheet2!$K3:INDEX(SCSS!$1:$65536,ROW(),usecol),$K3:INDEX($1:$65536,ROW(),usecol)),NA())

I'm not sure whether you want K3 in the above formula to be absolute or not.
Equally, I don't know whether you want to fix ROW() or make 1 part of the
formula ROW()-1

Anyway, as I said play with this idea and see if it helps.