INDIRECT with Sheet Level Defined Name
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. |
INDIRECT with Sheet Level Defined Name
Whoops! I meant I've tried this...
{=SUM(INDIRECT("'" & F2 & "'!Length_List")*INDIRECT("'" & F2 & "'! Used_List"))} |
INDIRECT with Sheet Level Defined Name
On Jun 17, 9:41*am, NickH wrote:
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 |
All times are GMT +1. The time now is 08:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com