Changing sheet name reference automatically

Not sure if I've understood you correctly but perhaps something along the lines of the attached... note the use of values in Cols U & V in the INDIRECT -- these values allow you to use one common formula across all cells in your matrix.

Re: INDIRECT... In short....

will execute the text string that results from within the outer parentheses as though it were entered as a formula directly...

=INDIRECT("A1")

is effectively like saying

=A1

If you take it further and say cell A1 holds the text B10

=INDIRECT(A1)

is like saying

=B10

If A1 held text B10 and say B1 held text "Sheet10"

Then this:

="'"&B1&"'!"&A1

generates a string of

'Sheet10'!B10

thus when the above formulae is encased within an INDIRECT( ) it essentially equates to entering the following formula directly, eg:

='Sheet10'!B10

So the key when creating an INDIRECT formula is to think as to how the formula would need to look were you entering it directly... a good tip is to create the formula normally... store the result as text (add ' in front of resulting formula) and then build a formula to generate the same text using dynamic variables in the string... once you know that the string you're generating equates to the formula string you can encase within the INDIRECT( )


So obviously Indirect is a very useful function but you should be aware that

a) it is Volatile which means the more of them you have and the bigger your model the greater the adverse impact they will have on the performance of your file (for more info on Volatility see: http://www.decisionmodels.com/calcsecretsi.htm)

b) it will only work where the Target resides in an open workbook...
people often think they can use Indirect to create dynamic references to external files -- eg on Weds 21st Jan I want to link to C:\Files\Jan21.xls but today I want to link to C:\Files\Jan22.xls ... this will work ONLY if the target (be it Jan21.xls or Jan22.xls) is open...
there are 3rd party utilities that can be downloaded to surmount this issue

So use wisely...