Sparkline

[Legacy 299145]

Afternoon,

I have the below data which operates on a rolling month.

Fixed Base Line	month 1	month 2	month 3	month 4	month 5	month 6
10	15	26	50	35	80	15

<tbody>
</tbody>

I am trying to put in a sparkline that automatically updates when the data changes. So I am deleting the month 1 column and adding a month 7 column onto the end however excel changes the references when I do this (useful normally). Is there a way I can keep the cell references for the data range the same when I delete the column.

Any help greatly appreciated.

Rich

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you copy months 2-6 into month 1 and change month 6 to month 7?

 

[RonB1111]

If your data is in A1:G2, insert 6 columns before column A so that your sample data is now in G1:M2. Now enter this formula in A2 and copy across to F2.

=INDIRECT(ADDRESS(ROW(),COLUMN()+7))

Now base your sparklines off of Cols A-F.
You can also copy the formula down.
You'll probably want to hide Cols A-F.

 

[Legacy 299145]

Welcome to MrExcel.

Can't you copy months 2-6 into month 1 and change month 6 to month 7?

Andrew,

That's worked, not sure why I didn't think of that one.

Thanks for the help