Sparkline

[Legacy 299145]

Afternoon,

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fixed Base Line
[/TD]
[TD]month 1
[/TD]
[TD]month 2
[/TD]
[TD]month 3
[/TD]
[TD]month 4
[/TD]
[TD]month 5
[/TD]
[TD]month 6
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]26
[/TD]
[TD]50
[/TD]
[TD]35
[/TD]
[TD]80
[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]

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