Using Named Range as single cell references in formulas that accept arrays

0
votes

So background first, question second.

I recently discovered an interesting property of named ranges that I'm experimenting with and not finding much help. The property is this: If I name a range (a column in this example), I can use the named range as a reference in formulas and it will usually resolve as though it were a reference to the same relative position as the current cell within the named range. So if I call A:A "Alphabet" and it contains letters a through z, each in their own row, I can simply type =Alphabet in cell b26 and it will evaluate to "z" (i.e. A26 instead of a:A). Seems simple, but it is shaping up to be quite powerful, because there is essentially an index function built-in. Very useful for making tidy formulas.

Onto the issue: when I use this same technique with a range that accepts an array argument (i.e. MAX, EOMONTH, etc.), the reference is resolving in the standard way (Max(A:A)). If I wrap the reference in VALUE(), then it resolves to just the single reference within the larger range (a26). The question is simply can anyone think of any way to avoid needing to do this, or at least to make the wrapper as unobstrusive as possible?

Real world example: I have a list of employees and I want to determine which date from three named ranges is larger. So I have something like ='DateSameRow1' > Max('DateSameRow2','DateSameRow3','DateSameColumn', and it is resolving as =a10 > Max(b:b,c:c,2:2). Note the issue: The named range outside of MAX resolved one way, and inside MAX resolved another. Sure, I could just write = a10 > Max(b10,c10,d2) or whatever, but this is so much more readable in what will end up being a huge formula. Right now I'm having to write MAX(VALUE('DateSameRow2')) or whatever to get the result I want and it is defeating the purpose.

Thanks

1
excelnamed-ranges
If you want this sort of functionality for "clean" formulas, you might benefit from wrapping a Table around the data and calculations. Within a table you get similar behavior with names. For same-row values you can use [@HEADEr], for the whole column [HEADER]. Entire rows (e.g. 2:2) don't work though. I would recommend you not build a spreadsheet relying on this behavior. It will be difficult to maintain and explain to others. It also leaves your work very vulnerable to the definition of the named range and its position w.r.t. your calculations. – Byron Wall

1 Answers

0
votes

You can use --NAMED_RANGE in front of the "offending" named range and it will force a negative VALUE and then undo the negative.

Per my comment, I would recommend you not build a spreadsheet on this functionality. The -- is even further removed from VALUE in indicating that a named range is being used in this way.