Ranyere has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Dynamically increase data range
Hiya,
I want to add sparklines to my report but am having an issue.
Every month I add a new column to my report. Say:
Column A B C
Jan Feb Mar
and say there is a sparkline whose data range is A2:C2.
For next month, when I add column D (April) to the report, how could I have the sparkline to automatically update its data range to A2:D2?
Thanks,
Ranyere
RE: Dynamically increase data range
Hello Ranyere,
To achieve what you require without writing a complex formula, you may find it easier to make your data area a Table. e.g. select range, Home Ribbon, Styles section, Format as Table.
Tables are dynamic by default. so once its a table, set your sparklines as normal, then if you add extra columns the sparkline with change automatically.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
RE: Dynamically increase data range
Hi Mark,
Many thanks for your reply.
What about the other option - writing a complex formula?
I would avoid making the area a Table if I could.
Thanks in advance,
Ranyere
RE: Dynamically increase data range
Hello Ranyere,
if you set up a named range, and in the data range area type the following: Change the G8 here with your first cell, and the P8 can be any, this is the end of your expected range.
=OFFSET(Sheet1!$G$8,0,0,1,COUNTA(Sheet1!$G$8:$P$8)-1)
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
