Is the MATCH worksheet function allowed in a named range for Excel charts?

0
votes

The following two named range definitions are valid (verified by Excel), and both highlight the same range of cells.

=OFFSET('AU_ARENDOFDAY Times'!$C$1, COUNT('AU_ARENDOFDAY Times'!$C$2:$C$10000) - 29, 0, 30, 1)

and 

=OFFSET(INDIRECT("'AU_ARENDOFDAY Times'!" & ADDRESS(MATCH(TODAY(), 'AU_ARENDOFDAY Times'!$A$1:$A$10000, 0), MATCH("RAXMTR_1", 'AU_ARENDOFDAY Times'!$A$1:$ZZ$1, 0))), -29, 0, 30, 1)

The first named range is accepted as a chart series, and it plots the data just fine. The second named range (pointing to the same cells) is not accepted. Excel throws up an error window saying, "We found a problem with one or more formula references in this worksheet."

I've tried using a static date in place of the TODAY function, but it still didn't work. That shouldn't be the issue, though, since Excel will highlight the appropriate cells for both named ranges.

The only thing I can think of is that Excel charts cannot use named ranges that include the MATCH function. Is this true? If so, is there an alternative way to accomplish the same thing?

2
excelchartsnamed-ranges

2 Answers

1
votes

If those are truly 'both highlight the same range of cells' then that is a peculiarity to your data. On two sets of sample data I got disparate results. It's not wise to rely on formulas that only work sometimes.

It seems that you want to chart the data in the column with RAXMTR_1 in the column header. In this case that is column C. You want a 30-day 'window' of data ending in TODAY().

Throwing out of all the volatile INDIRECT, OFFSET and ADDRESS functions, this may be a better formula.

=INDEX('AU_ARENDOFDAY Times'!$A:$ZZ, MATCH(TODAY(), 'AU_ARENDOFDAY Times'!$A:$A, 0)-29, MATCH("RAXMTR_1",1:1, 0)):INDEX('AU_ARENDOFDAY Times'!$A:$ZZ, MATCH(TODAY(), 'AU_ARENDOFDAY Times'!$A:$A, 0), MATCH("RAXMTR_1",1:1, 0))

This does produce results identical to your second formula. There should be no issue using it as the Refers to: in a named range (assuming that you have sufficient data to allow a 30 day backstep).

Your question is decidedly short on details about your data. Using MATCH(TODAY()-30, ... might be a better formula than subtracting 29 from the row returned by matching TODAY but that would only work if the days were sequential.

fwiw, that formula remains volatile as the TODAY function is volatile as well.

1
votes

For the second defined name try this:

=OFFSET('AU_ARENDOFDAY Times'!$A$1,MATCH(TODAY(), 'AU_ARENDOFDAY Times'!$A$1:$A$10000, 0)-1-29,MATCH("RAXMTR_1", 'AU_ARENDOFDAY Times'!$A$1:$ZZ$1, 0)-1, 30, 1)

Hope this helps. I was able to put that in a defined name an then plot the data. It may be INDIRECT that doesn't work in defined names.