 # FORECAST, in single cell, from 4 sheets?

• Hi to all!
Please, help. I need single cell forecast formula from four sheets. I already have concept(formula), but I don't know how to combine formula w/o helper columns X's Y's.

I'm stuck here: =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),HOW TO INSERT HERE "XY" FROM ALL 4 RESERVOIR?)

https://1drv.ms/x/s!ArVOOVnFRGX4g-4E1j0CApEasV2CtA

• Re: FORECAST, in single cell, from 4 sheets?

Hi again!

What do you mean by "XY" here, as in ""XY" FROM ALL 4 RESERVOIR"?

Also, what should the result be, and why?

Regards

• Re: FORECAST, in single cell, from 4 sheets?

=FORECAST(x,known'x,known'y)
I have result for one reservoir(R-5), it's in OneDrive's workbook. I'm in search for fuel height(centimetres) in reservoir. I Know Reservoir number, median temperature, and litres at 15oC. Tables from sheets R-5, R-6.... are litres at 15. When fuel is above 15, then fuel is thicker(spread)(low density), when below 15oC then thinner(compress)(having a high density)

• Re: FORECAST, in single cell, from 4 sheets?

Maybe this Gargantula =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),(INDEX(IF(D6="R-5",'R-5'!A4:B243,IF(D6="R-6",'R-6'!A4:B241,IF(D6="R-7",'R-7'!A4:B245,IF(D6="R-13",'R-13'!A4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1),1)):(INDEX(IF(D6="R-5",'R-5'!A4:B243,IF(D6="R-6",'R-6'!A4:B241,IF(D6="R-7",'R-7'!A4:B245,IF(D6="R-13",'R-13'!A4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),0),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1)+1,1)),(INDEX(IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1))):(INDEX(IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1)+1)))
Where there is : and (

• Re: FORECAST, in single cell, from 4 sheets?

Ok, but I don't think anyone on this forum will be prepared to decipher that monster! When I said "Also, what should the result be, and why?", I was rather hoping that you could say which values from which sheets should ultimately be included in the FORECAST function.

Regards

• Re: FORECAST, in single cell, from 4 sheets?

Sheets: R-5, R-6, R-7, R-13, Factors.
Main sheet with Forecast formula. My problem is, now this gargantula is volatile! Is it because sheet links??

I updated my OneDrive workbook. Thanks in advance!

• Re: FORECAST, in single cell, from 4 sheets?

Quote from B.W.B.;778368

Sheets: R-5, R-6, R-7, R-13, Factors.

Ah, doesn't look like we're getting very far. Of course I'd need to know which values from those sheets, not just the sheets themselves.

I could probably work it out using the gigantic formula you posted, but I'm sure you'll understand if I pass.

Hope someone else picks up on this thread shortly who can help.

Regards

• Re: FORECAST, in single cell, from 4 sheets?

Actually, I knew this formula, thanks to TomS Microsoft Community. I was hoping someone could know why this jumbo crazy formula is volatile. It's enough explained in uploaded workbook. Thanks anyway. God bless us all.

• Re: FORECAST, in single cell, from 4 sheets?

[TABLE="width: 1170"]

[tr]

[TD="colspan: 13"]FORECAST(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);(INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1);1)):(INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);0);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1;1));(INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1))):(INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1)))[/TD]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[/tr]

[/TABLE]

• Re: FORECAST, in single cell, from 4 sheets?

I'm sorry, copied Gargantula again. Sorry.

• Re: FORECAST, in single cell, from 4 sheets?

There is no way! Eventually i ended up using helper cells A1:A4, it's such a Forecast formula, unable to handle array, in any way, Index, sumproduct....
=FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),A1:A2,A3:A4) it's working marvelously. Thanks to everyone!