title: Referencing Dynamic Named Range in Excel Formula
body: <p>I have a table in Excel with column headings that correspond to part of a dynamic named range elsewhere in my workbook.  For example, I have these column headings: "10", "20", etc., and these dynamic named ranges: "ExampleRange10", "ExampleRange2", etc.  I'd like to enter a VLookup formula that references ExampleRange10 by concatenating the string "ExampleRange" and the column heading "10".  This would allow me to simply extend the formula across all columns in the table, instead of manually typing "ExampleRange10", "ExampleRange20", etc. in each column's formula.  </p>
  
  <p>I'm aware of the INDIRECT function, and have used it successfully in the past with named ranges, but it doesn't seem to be working with the <em>dynamic</em> named range in this case.  I'm guessing this is a nuance that has something to do with how dynamic named ranges are defined by Excel (they don't show up in the named range dropdown to the left of the formula bar, and they have some interesting properties in VBA, for example).  Is there a way I can use the INDIRECT formula in conjunction with a dynamic named range, or is there another way that I can go about solving this problem?</p>
  
  <p><strong>Edit</strong>: Here are the exact formulas used.<br>
  This is the main formula: <code>=VLOOKUP(B2,INDIRECT("ExampleRange"&amp;C1),2,FALSE)</code> where C1 contains "10" and the formula for my dynamic named range called "ExampleRange10" is: <code>=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2)</code>.  The main formula returns "#REF!", but it works correctly when I remove the dynamic named range formula and simply define "ExampleRange10" as a static range.</p>