If Statements and Charts with Data Series

[chrono2483]

Hello,

I have a spreadsheet, that has a graph chart. There is two sets of data within it, with 2 data sources. Within the Select Data Source, I have the two Legend Entries (Series) listed, which I want to be dynamic the Horizontal (Category) Axis Label will be static, as the data here is the hourly time intervals.

What I want to do is, based on the option selected within a drop down in cell A1, I want the Series data to pull the data for that respective option.

This is an example of the current data series formula used, to pull the respective data for each interval:

=SERIES("Support",('Inbound Queue'!$A$82,'Inbound Queue'!$A$86,'Inbound Queue'!$A$90,'Inbound Queue'!$A$94,'Inbound Queue'!$A$98,'Inbound Queue'!$A$102,'Inbound Queue'!$A$106,'Inbound Queue'!$A$110,'Inbound Queue'!$A$114,'Inbound Queue'!$A$118,'Inbound Queue'!$A$122,'Inbound Queue'!$A$126,'Inbound Queue'!$A$130,'Inbound Queue'!$A$134),('Inbound Queue'!$D$82,'Inbound Queue'!$D$86,'Inbound Queue'!$D$90,'Inbound Queue'!$D$94,'Inbound Queue'!$D$98,'Inbound Queue'!$D$102,'Inbound Queue'!$D$106,'Inbound Queue'!$D$110,'Inbound Queue'!$D$114,'Inbound Queue'!$D$118,'Inbound Queue'!$D$122,'Inbound Queue'!$D$126,'Inbound Queue'!$D$130,'Inbound Queue'!$D$134),2)

If there a way to convert that to an if statement, so that if A1=Inbound Queue, it uses the above data, if A1=Outbound Queue, it references the 'Outbound Queue' tab, but the same cell references ($A$82, etc).

Thanks.

 

[oldbrewer]

could you make inbound one data range and outbound another ?

 

[chrono2483]

Hi oldbrewer,

In this case, I would only want one set of data to be visible, based on the drop down selected in A1. The data would have no relation to each other.

Hope this helps. Thanks!

 

[oldbrewer]

			col D								col L
	row 5			inbound							outbound
			08:00	7						08:00	4
			09:00	11						09:00	9
			10:00	13						10:00	16
			11:00	18						11:00	19
					col F
						data for chart	inbound or outbound from a drop down
								outbound
			row 20		1		08:00	4
					2		09:00	9
					3		10:00	16
					4		11:00	19
						formula giving 4 by 08:00
						=OFFSET($D$5,F20,MATCH(I$19,$E$5:$L$5,0))

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>