Dynamic Chats

I have a table with 10 rows and columns are added weekly. The tricky part is that I only chart A3 and a4 rows of data of the table. Is there a formula to make my chart update automatically? or if there is already a KB point me in the right direction please.

Thank you!
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
i am not sure, if i understood your question correctly,

assuming if your question is that everytime your data expands and you only require the 3rd and 4th rows of each batch.
then it can be done with Dynamic named range using Index and and match with Mod functions.

if i would have a sample of your data, i can built the named dynamic range for you.
0
Tj aCommented:
The best way to get dynamic charts is to use Pivot Charts and Tables. Any of your changes will reflect instantly on your chart
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Yes my data starts in A2 and ends at A11 and my columns will keep growing with each week of data. I only need to chart A3 and a4 thru as many columns that I have. I hope that helps.
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Edward PamiasTeam Lead RRS DeskAuthor Commented:
Takunda Jora - I am already using a table. For some reason the chart does not update.---
0
Saurabh Singh TeotiaCommented:
epamias,

Can you post a sample workbook as it will be easy to build this solution post that...

Saurabh...
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
here is a sample sheet. All I want are rows a3 and a4 charted and of course the dates across.
samplesheet.xlsx
0
Rob HensonFinance AnalystCommented:
Would it be possible for you to get your table formatted the other way round?

With the Row headers across the top and dates down the side instead. Then the rows will grow and the table size will be automatically recognised.

When setting the graph data it is probably easier to select the whole table rather than the two rows (currently) or columns (if you transpose it) and then just hide the series that you don't want on the chart.

Thanks
Rob H
0
Saurabh Singh TeotiaCommented:
Their you go i created a dynamic chart for your reference for the first 2 row values..Now as and when you add additional data in the table..chart will automatically update itself...

Saurabh...
samplesheet.xlsx
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
I just realized I put chats instead of charts. LOL!. I need to read before submitting.
0
Saurabh Singh TeotiaCommented:
epamias,

Happens..Does the solution provided works for you??

Saurabh...
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Saurabh,

This seems to work but it does not explain to me how it was done. Can you provide the information?
0
Saurabh Singh TeotiaCommented:
Okay i followed these steps...

1. I went to formulas-->Name Manager--> Created three dynamic names for your data...What happens once your data increase these 3 dynamic ranges auto adjusted itself...`

2. Post creation of these dynamic ranges.. I inserted a manual chart and then added all these dynamic ranges as the series in the chart which act as a input for chart and dynamic ranges...
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Saurabh Singh Teotia,

I did the same thing on my sheet but the changes we not staying. I will try again and let you know.
0
Saurabh Singh TeotiaCommented:
When you did those changes while creating series you need to add workbook name in front of it..Check my individual series in the chart..not the chart source as that will be incorrect..Please check individual series...
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
I see the difference between your sheet and mine.... my sheet is using b1 b3 b4, you are using b1 b2 b3.
0
Saurabh Singh TeotiaCommented:
Ahh okay so it worked for you??
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
No.... it did not work..... can you change the sample sheet to use B1, B3, B4?
0
Saurabh Singh TeotiaCommented:
I'm assuming this what you are looking for..Basis of B1,B3 and b4..

Saurabh...
samplesheet.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Edward PamiasTeam Lead RRS DeskAuthor Commented:
This was perfect. Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.