@bebo021999

The Named Ranges you have chosen are not 'dynamically' defined.

A 'dynamically' defined range would be, for example:

=$A$1:INDEX($A:$A,MATCH(88^88,$A:$A))

which would create a dynamic range from A1 up to the cell containing the last numeric within column A.

If this Named Range were called MyRange, and cell B1 contained the string "MyRange", then:

=SUM(INDIRECT(B1))

would produce a #REF! error, since, as the OP points out, dynamically defined Named Ranges cannot be passed to INDIRECT.

@gak67

You can get round this issue using EVALUATE (though note that this requires that the workbook be saved as macro-enabled), viz (continuing the example from above) a further definition:

Name: MyRangeEval
Refers to: =EVALUATE(Sheet1!$B$1)

After which you can enter:

=SUM(MyRangeEval)

within the worksheet.

Regards