named ranges and data validation

[jules meredith]

I have a spreadsheet which has several named ranges for data. I can then use these ranges in formulas if I reference them directly by name e.g. =SUM(namedrange1). This works fine as expected.

However if I create a data validation list in cell A1 with a list of named ranges eg "namedrange1", "namedrange2" .... etc. and then write the formula =SUM(A1) I get 0 returned

How should I format my formula to use the current selection in cell A1.

many thanks in advance

 

[IDidNotDoIt]

Hi, JM.

Dows this happen even when the value 'namedrange1' is selected at A1? Or only when no value is chosen?

Regards!

 

[jules meredith]

Yes, it does not matter what is selected in A1, it can have any of the numerous "namedrange#" selected and it still returns "0".

 

[rahulshewale1]

Hi @jules meredith ,

try this ?

=SUM(INDIRECT(A1))

 

[jules meredith]

Thanks Rahul - that works

 

[rahulshewale1]

Welcome

 

[Peter Bartholomew]

An alternative approach is to use
= CHOOSE( A1, NamedRange1, NamedRange2, NamedRange3, NamedRange4, NamedRange5, NamedRange6 )
Going from a 100 ranges to a 105 would be pretty tedious, however.