Indirect and dynamic named ranges

[jamtay317]

Hello, I have a several dynamic ranges that are similar to:

=Sheet2!$C$2:INDEX(Sheet2!$C:$C,MATCH(9.99E+307,Sheet2!$C:$C))

and then on another sheet (same workbook) I have a data validation list with a list of the named ranges.

Im useing this formula:

=SUM(INDIRECT(C2))

and getting a #ref error. Please advise.

 

[jsotola]

what is the value in C2? (not the formula)

 

[jamtay317]

it is Test Range 1

 

[jsotola]

that is your problem

this is not a valid command

=SUM(INDIRECT("it is Test Range 1"))

 

[jamtay317]

https://www.dropbox.com/s/g9egifwe8bh2lpx/in.xlsx


here is the file that I have created similur

 

[Jonmo1]

The problem is pretty much that Indirect doesn't work with a dynamic range.
I'm not aware of any workaround (not saying there isn't one, I'm just not aware of it)

The named ranges pretty much need to be kept static to be used with Indirect.

 

[pgc01]

Hi

Indirect does not work with dynamic named ranges.

2 options

1 - If possible forget the dynamic range. In man«y cases you can use a static range. You are summing the values from C2 to the last number in column C. Since the Sum() function ignores the blanks you can simply define de named range as

=Sheet2!$C$2:$C$1000

or choose other last row that you're comfortable with

2 - use vba to get the range.

 

[jamtay317]

thank you.