Working out matrices where the location of one of the matrices must first be determined

[Excel_Arate]

Hello

I'm stuck on this. I have a matrix on a main sheet, consisting of one row of 14 variables. From this matrix I wish to subtract a second matrix and each of the differences is to be multiplied by a weighting factor.

The difficulty is that this second matrix must be chosen from the right worksheet. The worksheet to be chosen will be determined by a scenario number (integers from -15 to +15, no zeroes). E.G. if the scenario is "1" then the matrix on the worksheet named "Scenario 1" is subtracted form the one on the main sheet (each of the differences are multiplied by the assgned weighting in the yellow section on the worksheet "Scenario 1".

If this sounds confusing then please look at the attached Excel file and things should become clear. Excel language in formulae is in German, but it should still be easy to get the idea.

Best regards,

Excel_Arate

[MrShorty]

I think the INDIRECT() function is the first thing that comes to my mind: https://support.office.com/en-us/art...1-92b6306fa261
=($A3:$N3-INDIRECT("'Scenario "&$O3&"'!$B$6:$O$6"))*INDIRECT(...)

[Excel_Arate]

Hi Mr. Shorty,
Many thanks, just what I wanted... and you make it look so easy
Best regards,
Excel_Arate