98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dynamically increase data range
Dynamically increase data range
Resolved · Urgent Priority · Version 2010
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
Fri 20 Jan 2012, 09:48Trainer Mark said...
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
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
Fri 20 Jan 2012, 10:11Trainer Mark said...
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
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. |
Excel tip:Reconciling a list to correspond with anotherMay have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way. |
