Using a Named Range by entering the Name in a cell

CADcliff

New Member
Joined
Jul 3, 2018
Messages
6
I have a series of formulas that reference a Named Range in the same Workbook. Sometimes I need to use a different Named Range in those existing formulas. I would like to put the Name of the Range in Cell B3, and use that cell in my Formulas. Then if I change the contents of Cell B3 to the Name of a different Named Range. All my formulas will now refer to the new Named Range.

My current formula is:
=IF(ISNUMBER(MATCH(F1144,_293,0)),C1144,"")
Where "_293" is the Name of the Named Range. (this works)
I would like to place _293 in cell B3 for this formula.
Then edit the formula to use the contents of Cell B3 as its "range" input.
Such as this:
=IF(ISNUMBER(MATCH(F1144,$B$3,0)),C1144,"") (this does not work)

I have been unable to find a way for my formula to interpret the contents of cell B3 an the answer to the "range" input of the Match function. When I evaluate the formula, I do not get a "TRUE" result when I should. Where this evaluation should return "TRUE" it returns "Volatile" instead.
How can I translate the contents of Cell B3, in my new formula, into a format that the Match function can read as a Range?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Thanks Marcelo! That works! I didn't understand that INDIRECT could work for a RANGE. Many may thanks!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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