If Statements and Charts with Data Series

chrono2483

Board Regular
Joined
Aug 23, 2014
Messages
160
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

chrono2483

Board Regular
Joined
Aug 23, 2014
Messages
160
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
col Dcol L
row 5inboundoutbound
08:00708:004
09:001109:009
10:001310:0016
11:001811:0019
col F
data for chartinbound or outbound from a drop down
outbound
row 20108:004
209:009
310:0016
411:0019
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,407
Messages
5,831,436
Members
430,070
Latest member
Renske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top