@Erosus
« is it possible to set a slider to show the individual amounts on the stacked chart e.g. (Layer 1, 2 or, 3) »
Without using VBA event handlers … well, sorta. Each possible layer (your term; data series or just series in the usual terminology) has to be predefined for the chart. While I do not see a way of completely hiding the presence of data for the "upper layers" (assuming that you include a legend), formulas that conditionally replace the data values and headings within those series' ranges can get you mostly there. I.e., the concept is that the data and headings to be charted are almost a user-selected copy (full set or subset) of the original data. See the using Slider worksheet in the attached workbook. The original data is mostly in hidden columns; I do sum values from a few columns together in some formulas, but that's specific to how I wanted to group my data. Example formulas, for headings and quantities:
=IF($T$4>=2,"Nuclear","")
=IF( $T$4>=2, G6, NA() )
(Cell T4 really does contain just a number, from 1 to 5, originating from the slider. A custom format supplies the text.)
If instead of a slider you use checkboxes, you could roll your own "slicers". See the using Checkboxes worksheet. Concept: Associate each checkbox with a distinct power of 2, sum their values (if checked), and use the resulting sum and the BITAND function in formulas to determine the values in the cells to be charted.
(Normally, I would hide the content of cell Q2, but I left it visible so you can see it change as checkboxes are checked/unchecked.)
I'm not sure what you mean by using a slider to control the charting of ranges of values. Include/exclude the charting of cells based on the magnitude of their (original or derived) values? You could presumably accomplish that using the same "original vs. (sub)set for charting" concept as above, and modified formulas.
I have no clue what was wrong with your "messed up" formulas, so I have no suggestions on how to prevent that.
Edit: clarified that only checked checkboxes are included in the sum
