On a Summary sheet in cell F3 I have an array formula...

{=SUM(Colour_0!Length_List*Colour_0!Used_List)}

....which works fine. Length_List and Used_List are dynamic named
ranges local to the sheet "Colour_0".

In cell F2 I have the name of the sheet - "Colour_0" as a heading. I
want to refer to this heading in the formula so that users can easily
expand the tool by copying the Colour_0 sheet, then writing the new
sheet name in row2 on the Summary and copying the formula across.

I've tried this...

{=SUM(INDIRECT("'" & F2 & "'!Length_List")*"'" & F2 & "'!Used_List")}

....but I get a #REF! error. Is there a way to make this work or is it
not do-able using sheet level named ranges?

Br, Nick.