As of right now, I made a dynamic named range (called US) for the "Surprise" column in the "Surprise Indices" tab - it should automatically expand every time a new value is entered in the column. Formula for the named range is as follows:
=OFFSET('Surprise Indices'!$C$21,0,0,MATCH(1E+306,'Surprise Indices'!$C:$C,1),1)
My problem lies within the second worksheet, "Surprise Index Output", in the "US Surprise" column. In that column, I'm trying to average a certain amount of numbers in the "US" named range that correspond with specified dates. The formula is as follows:
{=IF(C21="","",AVERAGE(IF((Dates>=C21)*(Dates<=D21),US)))}
As of right now the column is producing exactly what I want it to produce: the average of the numbers in the US named range that are >= 1/1/2007 and <= 4/1/2007. However, instead of manually typing "US" in the formula, I'd like to reference it to another cell (cell E12) that has "US" typed in it. Then, if I wanted to look at another region, say "UK", all I'd have to do is type "UK" in cell E12 (assuming I had a dynamic named range for "UK" of course...). I tried using "Indirect(E12)" as a substitute for "US" but no luck.
Apologies - somewhat confusing (to me at least?) so I tried to lay out my example workbook in a clean manner . Thank you very much for your help - look forward to hearing a response!
Evan
Referencing Dynamic Named Ranges in Formulas
-
- NewLounger
- Posts: 4
- Joined: 24 Sep 2010, 22:35
Referencing Dynamic Named Ranges in Formulas
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referencing Dynamic Named Ranges in Formulas
Hi Evan, welcome to Eileen's Lounge!
Unfortunately, INDIRECT doesn't work with dynamic named ranges. Based on ideas from Daily Dose of Excel » Blog Archive » INDIRECT and Dynamic Ranges, here is a custom VBA function you can use:
DIndirect stands for "Dynamic Indirect".
Create a new module in your workbook and copy this code into it.
Then change the formula in Surprise Index Output to
=IF(C21="","",AVERAGE(IF((Dates>=C21)*(Dates<=D21),DIndirect($E$12))))
Just like the original formula, this is an array formula, confirmed with Ctrl+Shift+Enter. You can copy the formula down.
Unfortunately, INDIRECT doesn't work with dynamic named ranges. Based on ideas from Daily Dose of Excel » Blog Archive » INDIRECT and Dynamic Ranges, here is a custom VBA function you can use:
Code: Select all
Public Function DIndirect(sName As String) As Range
Dim nName As Name
Application.Volatile
Set DIndirect = Evaluate(sName)
End Function
Create a new module in your workbook and copy this code into it.
Then change the formula in Surprise Index Output to
=IF(C21="","",AVERAGE(IF((Dates>=C21)*(Dates<=D21),DIndirect($E$12))))
Just like the original formula, this is an array formula, confirmed with Ctrl+Shift+Enter. You can copy the formula down.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 4
- Joined: 24 Sep 2010, 22:35
Re: Referencing Dynamic Named Ranges in Formulas
That's perfect - Thank you very much Hans.