Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 17th 11, 03:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 60
Default 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.

  #2   Report Post  
Old June 17th 11, 03:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 60
Default INDIRECT with Sheet Level Defined Name

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

{=SUM(INDIRECT("'" & F2 & "'!Length_List")*INDIRECT("'" & F2 & "'!
Used_List"))}
  #3   Report Post  
Old June 22nd 11, 06:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDIRECT and CONCATENATE in Defined Name BillCPA Excel Discussion (Misc queries) 2 April 22nd 09 04:50 PM
Use of Indirect in Defined Name Frank Excel Discussion (Misc queries) 5 January 8th 09 09:40 PM
INDIRECT and Defined Names Tevuna Excel Worksheet Functions 1 September 4th 07 08:10 AM
Pivot Table - report product that have sales above defined level richard Excel Discussion (Misc queries) 0 December 9th 05 02:02 PM
Sum to Defined level Nate Walsh Excel Discussion (Misc queries) 2 March 14th 05 01:54 AM


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017