Dynamic Chart

Pinkoto

New member
Joined
Oct 8, 2016
Messages
8
Reaction score
0
Points
0
Hello,

Im using offset and name ranges to create a dynamic chart. It has both X and Y axis. The idea is to change the information with a drop down menu. So sometimes columns or rows have to be removed automatically. But when i remove a row or columns it gives me an error: excel found a problem with one or more formula references in this worksheet.

Does anyone know how and if this can work?

i cant make it in to a table also because when a value is deleted from the table the chart doesnt update properly (It is left half empty, and i want the information that is left to be properly distributed in the whole chart)

Thank you
 
Not without details of the data and the formula.
 
the information from A5 to G5 goes to the chart. I have made every row from 1 to 5 in a different name range with the offset formula. The Numbers in column "I" say how long will the vertical name range for each row be. I want to be able to make some of the rows zero so the chart will show if i want only one continent and one month or whatever value is put in the column "I".
Untitled.png
 
That doesn't help us tell you why your formula isn't working!

Attach the workbook here, in desensitised format.
 
In this file i have made name ranges for every row. When i change the value in column "I" to be a lower number the months will be removed from the chart - which is exactly what i want. But when i add a 0 to column I so the country will be removed it gives me an error. Also i dont want only the value to be removed but the name of the continents from the legend of the chart to be removed too, so the remaining continents will be able to spread across the chart equally. Thank you!
 

Attachments

  • TEST01.xlsx
    13.6 KB · Views: 20
I would first normalise the data using PowerQuery into a flat three-column table (continents, months and values), and then use this to generate a pivot chart. See the attached file with the three stages.
 

Attachments

  • Pivot Chart AliGW.xlsx
    32.2 KB · Views: 23
so it cant work with the offset? The reason i want it to make it work is cause my actual worksheet is a lot more complicated. I have six dropdown menus which take data from my 1000 rows database and insert it in to a table 21 rows high and 13 columns long and the chart shows the values from this table. And because of the filters my dropdown menu gives i dont think this
 
I would venture to suggest that your approach is overly complicated, but without seeing the real data, it's hard to judge. I have gone down that route myself in the past, and because the complex formulae necessary with a large dataset using OFFSET and the like can slow the sheet down, I have started to investigate more efficient approaches. You may need to have a similar rethink before you find yourself in a cul-de-sac.
 
Last edited:
so you dont think the offset formula can be made to work?
 
I didn't say that. What I said was that I would not go down that route. I am happy to suggest an alternative if you can supply a more representative sample of data, but even if you do solve it with OFFSET for your simple example, I suspect (no, I know) you will be getting yourelf into a world of pain trying to adapt it for your more complicated dataset. Been there, done that, got the T-shirt! It's up to you - I have suggested one solution, and am happy to offer advice. If you are dead set on the OFFSET idea, then you will have to hang in here for someone else to help you.
 
your solution doesnt work for what im doing, so i hope someone can help :)
 
the information from A5 to G5 goes to the chart. I have made every row from 1 to 5 in a different name range with the offset formula. The Numbers in column "I" say how long will the vertical name range for each row be. I want to be able to make some of the rows zero so the chart will show if i want only one continent and one month or whatever value is put in the column "I".
View attachment 7238

Why don't you add a pivot chart and a slicer to filter it?
 
because i want the drop down menu to work. Its a much easier user interface. I have made it to able to compare data from my main data source in a way that it wont work the way you are saying. The table and the formulas ive made works. Ive used index and ifs. But the chart based on that table doesnt work. So thats why i want to make it work with an offset function.
 
If the chart based on the table doesn't work, that means the table and formulas that you have made do not work. You can't have one without the other.

Google dynamic charts, there are a million examples on the net of how to do it the hard way.
 
Couldn't resist.
 

Attachments

  • ExcelGuru 8227-Dynamic-Chart.xlsx
    14.3 KB · Views: 13
with the example you gave us the chart looks half empty when all the months arent included. Also i dont think it can be made to work to compare different countries but only show one.
 
Ah well, I tried. It could be made to cater for more countries, but not by me, I am done.
 
Back
Top