forum.ozgrid.com/index.php?attachment/57472/
Hi All -
I currently have a file attached that can create waterfall charts, but it will only take into account one data set and create 1 waterfall chart. However, what I need for the script to do is the ability to create a multi stacked waterfall chart if needed (which I'm trying to do).
The 2 data sets are attached in the workbook.
Please let me know if you have any questions.
Re: $100 to change VB script to create a multi stacked waterfall chart
when you say multi, how many at most are they?
Re: $100 to change VB script to create a multi stacked waterfall chart
Hi SMC -
Currently there are 2, but it would be good to have the functionality to have multiple data sets incorporated in the future if it isn't going to be difficult.
Re: $100 to change VB script to create a multi stacked waterfall chart
There are two of what? Two series (Effect and Base), or two sets of bars in the graph (one between and including Base 1 and Base 2, and the next between and including Base 2 and Base 3)
Re: $100 to change VB script to create a multi stacked waterfall chart
Hi SMC -
What I meant by 2 sets of data is both graphs should be merged into a stacked waterfall graph. Meaning Chart 1 would be one set of base 1 and 2, but this also corresponds with Base 1 and base 2 (with the corresponding effects).
Chart 1 and 2 are graphing two different customer segments in 1 state so the current script can only show them as independent graphs vs an aggregated graph.
Re: $100 to change VB script to create a multi stacked waterfall chart
Another way to think about this is as a stacked bar chart between chart 1 and chart 2, but in waterfall format.
Please let me know if you have further questions.
Re: $100 to change VB script to create a multi stacked waterfall chart
Can you share a sample of how the final graph would look like?
Re: $100 to change VB script to create a multi stacked waterfall chart
Sure -
[ATTACH=CONFIG]57475[/ATTACH]
It would look something like this hopefully. Not sure if the pic is big enough to see, but was found here: http://peltiertech.com/WordPre…all-charts-bridge-charts/ (halfway down the sheet)
Re: $100 to change VB script to create a multi stacked waterfall chart
OK, so you are only looking for a stack of one set of bases. And not a stack with multiple sets of bases.
Re: $100 to change VB script to create a multi stacked waterfall chart
Yes,
i.e. if chart 1 and chart 2 were merged and hypothetically if base 1 = 60 and base 2 = 40 to total 100 it would be 1 stacked column split between 2 colors.
Re: $100 to change VB script to create a multi stacked waterfall chart
Hi all,
If SMC does not take this job, I could jump in. I have availabilities today and tomorrow.
Wigi
Re: $100 to change VB script to create a multi stacked waterfall chart
Wigi, happy for you to go ahead.
Re: $100 to change VB script to create a multi stacked waterfall chart
Thanks Sam.
So Hoowzer, you would basically like to have a sheet like "1 Chart", and have 2 tables of input and calculations.
Then, a button that when clicked, updates a "combined" waterfall chart (the result of the 2 sets of data, and produces a result visually similar to what you had in the picture in post #8) ?
It will always be 2 individual sets of data that need to be combined into 1.
Re: $100 to change VB script to create a multi stacked waterfall chart
Hi Wigi -
It will not always be 2 data sets. Sometimes it may only be one.
Re: $100 to change VB script to create a multi stacked waterfall chart
Is this what you are looking for ?
Sub M_snb()
c00 = Tabelle1.Range("G7").Resize(Tabelle1.Columns(7).Find("*not*", , xlValues, 2).Row - 7).Address
For j = 1 To 10
With Tabelle1.ChartObjects(1).Chart
.SeriesCollection(1).Formula = "=series('1 Chart'!" & Range(c00).Cells(1).Offset(-1, j).Address & ",'1 Chart'!" & c00 & ",'1 Chart'!" & Range(c00).Offset(, j).Address & ",1)"
.Refresh
End With
Stop
Next
End SubRe: $100 to change VB script to create a multi stacked waterfall chart
Hi SNB,
I'm not sure I'm following the code you posted. How would this be implemented in the file that was shared in the OP?
Re: $100 to change VB script to create a multi stacked waterfall chart
Hello Hoowzer
So, either 1, either 2 of the ranges B2:R25 ?
Re: $100 to change VB script to create a multi stacked waterfall chart
Hello Snb
What is x5 in your code?
Re: $100 to change VB script to create a multi stacked waterfall chart
Hi Wigi-
In this case it would be 2 ranges as mentioned, but would like the flexibility to add more as needed.
Re: $100 to change VB script to create a multi stacked waterfall chart
Hello,
And in the case of more than 1 range, where/how would you do the input?
Will you use a sheet where x number of ranges are setup
(the formulas exist but the range could be left as such if it does not need to be used by the user) ?
Don’t have an account yet? Register yourself now and be a part of our community!