How to enter disjoint cells as y-values in chart

[kalliklles]

I have a table organized as follows (simplified)

    A       B     C   D   E   F   G   H    I     J 
    1     Week #  M   T   W   Th  F   S   Su  Totals  
    2        1                                    10
    3                                            500    
    4        2                                    15
    5                                            400
    6        3                                    23
    7                                            350

Every row of every week represents a value and I would like to chart them as series. In other words, my 2 data series (in the example) are: (J2,J4,J6) and (J3,J5,J7) with y-values of (10,15,23) and (500,400,350).

How can I enter the correct references for a series of disjoint cells in the chart wizard? It only seems to accept contiguous areas with the area selection button. Or do I have to manually type the lists? The real ones contain several dozen cells each…

[kalliklles]

Well, since I could not find a simpler solution and refused to enter the refs manually, I ended up creating an auxiliary table that filtered only the values I was interested in and charted that. Basically, I created a pivot table manually, by writing down the number of weeks, the number of rows per week, and the horizontal and vertical offsets for the data points, and then filling a table with @index formulas.

In the example above, and for future reference, that meant creating a table as follows:

Number of weeks:           3
Week block length:         2
Totals col offset:         8
First series vert offset:  0     
Second series vert offset: 1 

Week   First series       Second series
1         10                   500
2         15                   400
3         23                   350

The data cells all contain the formula: =index(‘Data_table’, week-1*week_block_length+ (First_series_Offset| Second_series_offset), totals_col_offset).

[catbill]

Unfortunately, Chart is not terribly clear about how to add non-contiguous cells to a selection. Fortunately, it is relatively easy to do so.

To create a data range from multiple cell areas that are not next to each other, the key is to use a delimiter between ranges. The appropriate delimiter depends on the locale selected at Tools > Options > Language Settings > Languages > Locale setting. You may need to experiment to find the appropriate one for your locale.

So, for example, the English (USA) locale uses a comma as a delimiter and “$Sheet1.A1:A5,$Sheet1.D1:D5” is a valid data range. A semi-colon is another commonly used delimiter.

To select non-adjacent data, do one of the following:

• Directly enter the delimiter between data ranges in the text box.
• Select the data with the cursor by first clicking the little Select data range box to the right of the Data range box. Place the cursor at the end of the first data range in the text box (otherwise the first range is selected and then deleted) and enter the delimiter appropriate for your locale setting. Then drag the cursor to select the next data range.

Hope this is helpful. Does it make sense?

[skyhook]

Excellent, thank you CB. I never would have guessed delimiter to be locale-specific. Online docs say to use semicolon but my LO just turns red in frustration. Comma works for me. Will start new question about labels, though.