Forum Discussion
Range name in formula
David2190 Let's say you enter the name in cell A1.
You can then use INDIRECT(A1) in your formulas to refer to the range whose name is in A1.
INDIRECT is the answer to the OP, but it has never been my favourite function because of its volatility. I set out to show an alternative that works when the ranges are collected together as a table. I could also have suggested
= LET( option, XMATCH(Name, Heading), SUM(CHOOSE(option, MyRange, YourRange)) )As SergeiBaklan points out, besides being aggressively 365, my solutions never use any direct referencing. That just stems from a remark I made that the A1 notation is an abomination that should never have been permitted notation anywhere within an electronic spreadsheet; I feel obliged to stick with my own assertion, even if it obscures my formulas for others.
I apologise for causing confusion; it is me not you!
On the other hand that narrows the solution. Let assume named ranges with data are kept separately, e.g. in different worksheets.
I confess I have very little experience of links between workbooks or between a workbook and a Word document. Even on those occasions, the focus tended to be on introducing VBA or PowerQuery to eliminate any direct links. Very rarely, I have used defined Names from the data source (rather as one would do from VBA) but I suspect that requires the workbook to be open, so I would still be back to relying on VBA to ensure the workbook is open (or even exists).
