Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dynamically increase data range
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Dynamically increase data range
Resolved · Urgent Priority · Version 2010
Reply Thu 19 Jan 2012, 16:33Delegate Ranyere said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Fri 20 Jan 2012, 09:52Delegate Ranyere said...
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
Reply Fri 20 Jan 2012, 10:59Delegate Ranyere said...
RE: Dynamically increase data range
Hi Mark,
I've just replied to forum
so if you could have a look, please.
Thanks in advance,
Ranyere
Reply Mon 23 Jan 2012, 15:55Delegate Ranyere said...
RE: Dynamically increase data range
Hi Mark,
Hope you're well.
I was wondering if you've had a chance to have a look at the sheet?
Many thanks in advance,
Ranyere
|