Graphs With Different Data

[shelby55]

Hello

I am using Excel 2002 linking to an Access 2002 database.

Thanks largely to Skip, I've been successful in developing a template that filters on Department and MonthEnding. The graphs developed to date have all been the same data (% sick time, % overtime and budget) but filtered for the different departments. All the graphs are based on named ranges using the offset function to handle the filter changes.

Now I want to produce graphs specific to each department but one graph might not be for another. For instance, for obstetrics I want to show c-section rate but that won't be applicable for other departments so I only want it to show when obstetrics department is selected. How is it best to deal with this i.e. how to make a graph visible or not depending on department? Because I want to format and place on the page correctly, would it make sense to have multiple graphs on top of one another and make their properties visible or not depending on department? If so, how would I do that?

I realize this may include VBA but wanted to start here before moving to the VBA forum. Thanks.

 

[SkipVought]

Hi,

If your data has a column for c-section rate and other rate, and both columns are named ranges using the column headings, then in your chart data sheet, you can change the heading for the column representing the series you want and use the INDIRECT function to refernece either rate, basaed on the series column heading (remembering that space and dash must be replaced by an underscore). The result is that your chart will either display c-section rate or other rate.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks Skip. So how would I handle headings that need to change for whatever is selected?

 

[shelby55]

Hi Skip

Sorry but I'm having difficulty with this.

Okay, I have two graphs, %ALC and %SickTime. The ALC graph series are based on:

%ALCDays = ='Clinical_Indicators_Nov 12_LOS_B.xls'!PC_ALC
target ='Clinical_Indicators_Nov 12_LOS_B.xls'!ALC_Trgt

PC_ALC is a named range:

=OFFSET(Finance_Raw!$AD$2,0,0,COUNTA(Finance_Raw!$AD:$AD)-1,1)

The Sick Time Graph series are:

% Sick Time='Clinical_Indicators_Nov 12_LOS_B.xls'!PC_Sick
Baseline='Clinical_Indicators_Nov 12_LOS_B.xls'!Sick_Bsln
Target='Clinical_Indicators_Nov 12_LOS_B.xls'!Sick_Trgt

The named ranges above are:

PC_Sick=OFFSET(Finance_Raw!$V$2,0,0,COUNTA(Finance_Raw!$V:$V)-1,1)
Sick_Bsln=OFFSET(Finance_Raw!$X$2,0,0,COUNTA(Finance_Raw!$X:$X)-1,1)
Sick_Trgt=OFFSET(Finance_Raw!$Y$2,0,0,COUNTA(Finance_Raw!$Y:$Y)-1,1)

All are based on the x-axis of:

='Clinical_Indicators_Nov 12_LOS_B.xls'!MyDates

The named range is: =OFFSET(Finance_Raw!$U$2,0,0,COUNTA(Finance_Raw!$U:$U)-1,1)

Obviously right now they are two graphs but how could I create one graph using Indirect?

Thanks.

 

[SkipVought]

First, these offset function should ONLY vary in the column offset, argument 3. Assuming your table begins in A1 AND column A is fully populated...
[tt]
PC_Sick=OFFSET(Finance_Raw!$A$1,1,22,COUNTA(Finance_Raw!$A:$A)-1,1)
[/tt]
So, for instance, if your PC_Sick was a dual use column, arg 3 (currently 22) would instead be an IF() function that would return the proper column offset for some given value that would indicate the chart. In this case, you would not use the INDIRECT function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Sorry but I'm still not understanding...so I should actually make the column that is currently PC_Sick (as an example) change to some other data item? Or use the offset function to point to a different column?

I'm still not sure what I would do to use in the "if" statement you are suggesting. For instance, let's say I want the PC_Sick Time to be the graph if department is Medicine and Surgery but not if it is Obstetrics....how would I code that into the current named range?

Just asking...but would it be easier to use the visible property for these graphs? I would have graphs pasted on top of each other and make them visible depending on certain criteria?

Thanks.

 

[SkipVought]

I advise against multiple charts, because each chart has a maintenance overhead.

I would look at any variable series, and code it generically. There are all kinds of ways to da that. One way is to use the OFFSET() function. You can name the range anything you want. But the return range can be siimply controlled by modifying the row or column offset and/or the row or column depth.

Lets say that you have a cell, (ZZ1) that when you choose to display the obstetrics chart, the value is c-section rate and otherwise its % sick time. Then the formula for your named range...
[tt]
PC_Sick=OFFSET(Finance_Raw!$A$1,1,MATCH(ZZ1,Finance_Raw!$1:$1,0)-1,COUNTA(Finance_Raw!$A:$A)-1,1)
[/tt]
assuming that c-section rate & % sick time are the headings in the columns for the series that you want to display.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but will this be affected by the filter that is set? Also, I'm not sure from the information that I provided you, how would I make generic?

I guess where I'm confused is where is there a flag for to use c-section versus sick time? Am I actually changing the data in Z1 to c-section versus sick time or just pointing the named range to a different column if the flag is one or the other?

 

[SkipVought]

Sorry but will this be affected by the filter that is set?

Of course!

Also, I'm not sure from the information that I provided you, how would I make generic?

I meant that the range name you have might not be applicable to multiple uses, so you might want to assign a more generic name for clarity.

Flag? I was casting around for a relevant term. Here's the bottom line: When you SELECT the obstetric chart, I assume you would do that from a combobox. SO... based on that selection, you would assign a value to ZZ1 or whatever cell you want to use. It could simply be a formula in ZZ1 like
[tt]
=If(SelectedChart="c-section rate","c-section rate","% sick time")
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Okay I think I have it. With the filter I meant that some of the charts that are going to show are based on the department filter i.e. if department = obs then c-section else if department = surgery or medicine then % alc days.

I'll give this a whirl and see what I come up with...thanks again!!