Jul 15 2021 03:00 AM
Hi
I want to use a named range in an index/match formula, but I want the range used to be determined by text in another cell. When the text in this other cell changes to another named range, the formula uses the new named range.
How would I go about doing that? The formula works splendidly with the range named specifically, but I need it to be dynamic.
Thanks.
Jul 15 2021 03:20 AM
Solution@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.
Jul 15 2021 03:50 AM
Jul 15 2021 03:54 AM
Jul 15 2021 09:05 AM
Jul 15 2021 09:47 AM
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 @Sergei Baklan 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!
Jul 15 2021 01:44 PM
On the other hand that narrows the solution. Let assume named ranges with data are kept separately, e.g. in different worksheets.
Jul 15 2021 03:35 PM
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).
Jul 15 2021 03:20 AM
Solution@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.