[Solved] Range within a range

[BrianDP1977]

This will probably be easiest if I break my question into three parts.

1) Is it possible to pass a chart named ranges (I’m going to assume yes but have not yet tried it because I want to get the other two questions asked)?

2) In charts (a stacked bar chart to be specific), is there a way to define two separate ranges of information to be displayed for a single series. For example, the value for a series would typically be defined by the range $H$4:$H$28. Is it possible to define it so that it includes separate ranges such as $H$4:$H$5$ & $H$10:$H$11 & $H$20:$H$21?

3) If all the above is true (primarily the 2nd one), how would one go about defining a range within a range. As evidenced by the 2nd question, I currently have a large series of data in a column defined from H4 to H28 (named Data_All). This column of data is further subdivided into three other sections. Let’s say H4:H9 (named Data_1) is one section, H10:H19 (named Data_2) is another, and H20:28 (named Data_3) is the final one. The current chart displays all the data values from H4 to H28. I would like to create code that, when a button is toggled on, it will change from the chart showing all the data to one showing truncated data (is that the right word?) which displays the first two cells in each of the three sections of the entire data range (the resultant example of $H$4:$H$5$ & $H$10:$H$11 & $H$20:$H$21). The section sizes may change over time so the code would have to use dynamic names to search through specified ranges to determine the first two cells in the specified range. Is this possible?

 

[nbrcrunch]

I can't help you directly, but you might find the following of interest:

http://peltiertech.com/Excel/Charts/

 

[BrianDP1977]

Thanks for the info. I haven’t had a chance to look at everything on the site but I’ve thought of something. Actually, from sitting back and thinking about it, I actually don't need to necessarily format the series range entry with non-contiguous data (i.e. the 2nd question). All I need to do is:

1) Figure out a way to define a new range which is defined as the 1st two rows of each of the original three sections. A less robust way to do this would be to output only the first two rows of each section (Data_1, 2, 3) to a new range of cells (Data_First_2_rows).

2) Then, upon a button press, change the series reference from the original data range (Data_All) to this new range (Data_First_2_rows). I’d rather just be able to define a range within a range (i.e. Set Data_First_2_rows equal to the first two rows of the sections Data_1, Data_2, Data_3 within Data_All (this last reference to Data_All is probably unnecessary since Data_1,2,3 are already defined)) and have this newly stored range called by the button to take the place of Data_All. However, I do not know how to figure out how to define this. Thank you for the help.

 

[BrianDP1977]

Got it worked out. Thanks.