Using a named range in formula, reading it as cell value

[roc_on_the_rocks]

Hi folks, I'd really appreciate some guidance, this is driving me nuts (and it's work related).

Let's say I have a range of values named 'apple', another named 'banana', and another named 'orange' (all w/o the quotation marks).

Then there's a cell where the user will select the fruit of choice (I'm facilitating that choice by using Data Validation drop-down listing the fruits).

Then a formula will read from that cell which named range is to be used in the calculation. I got it to work once, than it doesn't work anymore, even in new files.

I'm obviously paraphrasing my problem, but this should get me going.

BIG THANKS in advance.

 

[DanteAmor]

Check if the following helps you:

varios 14jun2023.xlsm
	A	B	C	D	E	F	G	H	I
1		Value			Fruits				apple
2	apple	24			apple	295			banana
3		99							orange
4		74
5		98
6	banana	25
7		96
8		15
9	orange	48
10		72
Hoja6

Cell Formulas
Range	Formula
F2	F2	=SUM(INDIRECT(E2))

 

[roc_on_the_rocks]

Check if the following helps you:

varios 14jun2023.xlsm
	A	B	C	D	E	F	G	H	I
1		Value			Fruits				apple
2	apple	24			apple	295			banana
3		99							orange
4		74
5		98
6	banana	25
7		96
8		15
9	orange	48
10		72
Hoja6

Cell Formulas
Range	Formula
F2	F2	=SUM(INDIRECT(E2))

Thank you very much Dante, but I don't know how this would help in my problem.

 

[Peter_SSs]

named range is to be used in the calculation.

What calculation?