Hello,
I want to make a dynamic chart that can grow or shrink both on the X and Y axis. In the table the months are put horizontally and the different departments are put in the different rows vertically. Which way do you think its the best to make the name ranges so it will work? Should i make them for each month vertically or for each department horizontal?
PS: The table can change from 1 row 1 column to 13 columns 21 rows, its controlled with drop down menus.
if i make the name ranges horizontally for each department the chart will work great only if you add or remove months. It will shrink and grow automatically. But if i remove or add a department the chart will remove the data but it will leave blank space in the place of the removed data.
The same thing will happen if i do it vertically for each month. If i add or remove department the chart will work ok but if i add or remove a month it wont remove the blank space.
I know it doesnt seem as a problem but if i remove 20 departments or 11 month the chart will be huge for the small information it will show.
Is there a way to import both vertical and horizontal name ranges in the chart? Or is there any other way to achieve this?
Without any details (what you are really trying to show, where the data is coming from, etc.) it is difficult to make any recommendations.
My first thought is to organize the data in a good database format, then use a pivot table and pivot chart to summarize and display the data. This assumes that what you are trying to do "fits" what a pivot table/chart can do. As it applies to your question, pivot charts are nice because they are very dynamic -- they respond to changes/filters/etc. in the pivot table automatically.
Without more detail, that is going to be my first recommendation. Look at what you are doing and see if it fits into something that a pivot table/chart can accomplish.
Originally Posted by shg
Can you upload sample file? Without it, I'd be taking a guess as to what sort of chart you've set up and how you defined named range etc.
To upload, go to "Go Advanced" and use Manage Attachments menu (short cut button isn't working at the moment).
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
im uploading a very simplistic sample of what im doing but its the same idea. The name ranges here are horizontal. if i change the number in column I the name range will grow or shrink and the chart will change depending on it. But if i remove a continent it wont completely be removed from the chart (the black space will still be there). Please add a comment if you know how to make this work in anyway. A pivot chart work for me.
Your result looks like a pivot table, so I would pursue a pivot table/chart solution.
0) Start with data in a good database arrangement:0a) If you don't have access to the original database, I understand that tools like Power Pivot have an "unpivot" command that can convert a table like in your example to a database list like I suggest.
Please Login or Register to view this content.
1) Insert pivot table with month/date as row labels and continent as column labels and sum of values as the value field
1a) If your month/date column contains real Excel dates, then you may need to use the Group command to group multiple dates during the same month into months.
2) Insert column chart based on pivot table and format accordingly. http://www.excel-easy.com/data-analy...ot-tables.html
so what i want cant be done? pivot table charts wont work for what im doing.
I did not say it cannot be done, I am optimistic that most anything can be done with enough time, energy, and ingenuity. I just said that, based on the example you gave, a pivot table seemed the easiest way to accomplish what you show in your example. A pivot table approach seems so much easier to me than other dynamic named range approaches, that I would ask back. What is different about your real data (that your sample does not show) that a pivot table will not work?
I have a 1000 rows database with 20-30 columns of different information for each department. With the help of the dropdown menu i choose which department for which month and year to be compared to which other department. I have made everything else to work. The table that is build with the dropdown menu works perfect. Now i just need to make a chart for this table that can change its size from 1 row 1 column to 21 row 13 columns. Please if you know how help me do it the way im askingthank you
In general, as MrShorty has indicated, it is a good idea to start from flat table structure for any sort of analysis and use PivotTables and other available tools.
With newer version of Excel, there are many tools available that simplify dynamic reporting along with more powerful analytics (Get & Transform comes standard now).
And PivotTable is one of the best tools out there for summarizing data.
Also as MrShorty wrote, most things are possible in Excel, difference is in which method offers more/less obstacle in achieving the end goal.
From the looks of it, what you are trying to achieve will need VBA, as without it, SERIES formula for removed groups cannot be removed from chart.
how hard will the VBA code be? is anyone more familiar with VBA and able to help?
I am still not seeing why a pivot table will not work.Pivot tables include built in filter dropdowns. If you design your pivot table correctly, it should still be simple dropdowns to choose department and month/year for comparison.With the help of the dropdown menu i choose which department for which month and year to be compared to which other department.
I'm sure we can help you with the VBA, if that's what you really want to do. However, it still seems more difficult to me to use VBA than to use a pivot table/chart for this.
i will be extremely grateful if you help me with the VBA. I havent seen anything like it on the internet. I have zero experience with VBA so it will be really hard for me to do on my own
I'll have to refresh myself on chart object model for VBA. Give me sometime.
I used to manipulate charts using VBA, but then I have since moved most of my reporting to PowerBI (using R script and other tools it's very powerful).
In the meantime here's demo of what can be done using PivotTable using slicer controls.
I transformed your data using Get & Transform into flat table structure, then based pivot table off of it. I put the table back on the sheet for demo.
But I'd recommend loading it directly to data model and create pivot table from data model.
Last edited by CK76; 08-24-2017 at 02:09 PM.
I like what you have done but i will have to make a really big table. Since there are 3 years 12 months 21 departments and 20 colums for different type of data for each of the departments. Also with my dropdown menu i have different type of comparision and i dont think it will be easy to create a different row for each one. Also the dropdown menu has a better user interface
I hope you are ready to customize VBA. Since I can only write based on your sample.
I'd recommend uploading workbook that accurately replicate your workbook set up. From the what you wrote, sample file is simplified version that lacks any dropdown that controls your chart.
In simplest form, following VBA will change chart source range.
See attached Sheet2.Please Login or Register to view this content.
thank you for the file! I prefer to send it via email instead of uploading it. If you want i can PM you.
Can anyone who knows VBA do the same thing as CK76 in the file he uploaded in sheet 2 but for the table in this post.
ps: i know the placement of the table is weird and that the chart has too much information but please anyone who can write in VBA and understands the code of CK76 it will be extremely helpful.
anyone ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks