Referencing Dynamic Named Ranges in Formulas

[Evanrich88]

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

's Example.xls

. Thank you very much for your help - look forward to hearing a response!

Evan

[HansV]

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:

Code: Select all

Public Function DIndirect(sName As String) As Range
  Dim nName As Name
  Application.Volatile
  Set DIndirect = Evaluate(sName)
End Function

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.

[Evanrich88]

That's perfect - Thank you very much Hans.