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

roc_on_the_rocks

Board Regular
Joined
Jun 6, 2009
Messages
175
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Check if the following helps you:

varios 14jun2023.xlsm
ABCDEFGHI
1ValueFruitsapple
2apple24apple295banana
399orange
474
598
6banana25
796
815
9orange48
1072
Hoja6
Cell Formulas
RangeFormula
F2F2=SUM(INDIRECT(E2))
 
Upvote 0
Check if the following helps you:

varios 14jun2023.xlsm
ABCDEFGHI
1ValueFruitsapple
2apple24apple295banana
399orange
474
598
6banana25
796
815
9orange48
1072
Hoja6
Cell Formulas
RangeFormula
F2F2=SUM(INDIRECT(E2))
Thank you very much Dante, but I don't know how this would help in my problem.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top