How to select cells (in a column) based on the value of adjacent cell for use in a chart or statistical function.

[ThomasJak]

Example: I have 3 columns. Col 1 is dates. Col 2 is numbers. Col 3 contains a 'A' or 'B' in each cell.

So normally I select Col 1 and Col 2 for a chart and all good.

But how do I select cells from Col 2 ONLY when an adjacent cell in Col 3 contains a 'B'?

Thanks

PS I do not mean manual selection of each cell. I imagine somehow I can select the 3 columns and somewhere tell the chart about the above scenario.
Perhaps create a query to feed the chart (I know queries well as I can program in VBA in Access) but I cannot readily see how to in Excel using what is available in the Data tab.

 

[MickG]

Perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG08Jul03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) = "B" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn.Resize(, 2) Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn.Resize(, 2))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData nRng
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ThomasJak]

Perhaps:-

[COLOR=Navy]Sub[/COLOR] MG08Jul03
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, nRng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn.Offset(, 2) = "B" [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] [COLOR=Navy]Set[/COLOR] nRng = Dn.Resize(, 2) Else [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn.Resize(, 2))
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData nRng
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thank you MickG and i will do that but I was hoping for a non-programming way such as a query feeding the chart.