I have a Excel Graphic that was done by an outstanding EE some time ago for me. I now have an immediate need to modify it but do not have the skills to do so. I have attached my attempt at modifying the graphic (XLSM) and a copy of the desired graphical result (embedded in the spreadsheet as a picture)..
In Summary;
It's a 12 category (y-axis) across a 5 category (x-axis) bar chart with arrow
Three levels of solid fill
Two data points at the top of the first two solid fills
And an Arrow that goes from the first point to the second point.
Much thanks in advance,
Bright01
Complex-Chart-Modificationv1.xlsm
I'm not so sure that aligning the chart with underlying columns is such a good idea. The Y axis labels will take up a varying amount of space, depending on how wide your longest title string is, and you'll be forever adjusting the columns underlying the charts.
If your title texts remain the same, though, you could try this:
Format the Chart Area to be transparent (no fill)
format the Plot area with white fill
remove the text boxes
set the primary and secondary X axes to a maximum of 5 (instead of 7)
resize the plot area horizontally to the right, so it is closer to the right hand side of the Chart Area, but leaving enough room for a full data marker.
Align the column widths behind the chart to match with the X axis major units.
Use the cells behind the chart to enter the text that used to be in the text boxes, format to suit.
I faintly recall that there was a macro that resized the plot area according to the number of line items in the chart. That macro made sure a certain space at the top of the chart, above the plot area was kept free to accommodate the text boxes. This may need to be revisited, and changed to let the chart defaults kick in. That way, the chart won't have to overlay the blue cells with white text.
The blue cells are a bit awkward to access at the moment, since you can't select them with the mouse. You need to select a cell outside of the chart area and then use the arrow keys to navigate to the blue cells. Fixing the macro mentioned above will get rid of the white space in the chart area, and the chart can be placed underneath the blue cells instead of overlaying them.
Does that make sense?
I can't make any structural changes to the sheet, since every time I even change a number, the change event kicks in and refers to a sheet that's not included in the sample file, and then bombs.
Ideally, I'd have moved the chart to an empty area of the sheet, where the column resizing does not affect any data.
I hope you get the idea of what I'm trying to do in the attached.
cheers, teylyn
Copy-of-Complex-Chart-Modificati.xlsm