Changing sheet name reference automatically

see INDIRECT function, eg:

SUMMARY!B3: =IF($A3<>"",INDIRECT("'"&$A3&"'!C8"),"")
copy down for all B formulae.

However you may find it easier if you store the appropriate cell to which you wish to refer in each column in a given row.. so let's say B15 holds text C8, C15 holds C5 repeat for all columns accordingly, then:

Summary!B3: =IF(AND($A3<>"",B$15<>""),INDIRECT("'"&$A3&"'!"&B$15),"")

The above formula can now be copied across the entire range...

Get the idea ?

For some like D you will need to append formula later to * result by 4 etc...

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...

Please Login or Register  to view this content.

is effectively like saying

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

is like saying

Please Login or Register  to view this content.

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

Then this:

Please Login or Register  to view this content.

generates a string of

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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...