I'm trying to use the sparkline function but I want to use some formula to represent the range instead of using a reference like J11:U11.

Let's say I got two address formulas

=ADDRESS(ROW(E11),MATCH(E11,$A$10:$CU$10,0),4) =ADDRESS(ROW(F11),MATCH($F11,$A$10:$CU$10,0),4)

which returns J11 & U11 respectively.

How could I get a result similar to =sparkline(J11:U11) with the formula above?

example

Thanks!

CodePudding user response：

Answer

This seems to be a bit of an XY Problem. If I understand correctly, there's a much easier way to accomplish what you wish than through the use of =ADDRESS.

For your example sheet, the formula below should produce the results you desire:

=SPARKLINE(FILTER($D5:5,$D$4:$4>=$B5,$D$4:$4<=$C5))

Explanation

The =FILTER formula can be used to only return the results from a range dependent upon a specific set of criteria. In this case, the =FILTER only returns the columns from row 5 where the row 4 entry of that column is between the specified dates.

More specifically, the filter returns those columns from $D5:5 where $D$4:$4 is after $B5 but before $C5. When this formula is placed in a cell and autofilled into the rows below it, Sheets automatically adjusts the relatively referenced portions of the formula for each new row you wish to calculate.

Functions used:

• =FILTER
• =SPARKLINE