INDIRECT with Sheet Level Defined Name

[NickH]

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.

 

[NickH]

Whoops! I meant I've tried this...

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

 

[Don Guillett]

Whoops! I meant I've tried this...

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

Here is a macro using a sumproduct formula that I did recently. You
should be able to modify to suit.

Sub DoSumproductFormulaToValuesSAS()
lc = Cells(2, Columns.Count).End(xlToLeft).Column
'MsgBox lc
lr = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox lr
Range("b3").Formula = _
"=SUMPRODUCT((INDIRECT(B$2&""!b$6:b$600"")=$A3)" & _
"*(INDIRECT(B$2&""!e$6:e600"")))" & _
"/SUM(INDIRECT(B$2&""!$E$6:$E$600""))"
'copy formula and convert to value
Range("b3").Copy Range(Cells(3, "b"), Cells(lr, lc))
Range(Cells(3, "b"), Cells(lr, lc)).Value = _
Range(Cells(3, "b"), Cells(lr, lc)).Value
End Sub