malik641
11-18-2005, 01:53 PM
First off, I'm kinda new to Chart creating....with formulas. I know how to make Charts of different types and how to add data and linked cells for titles, etc...
But what I'm looking to do I have a feeling may not be done so easily...
Here's the premise:
I have 3 sheets: 'Forecast', 'Actual', and 'Results (Days Late)'
Forecast sheet:
Has projected dates for numerous headings (Sample Receipt Date....Repeat Analysis date....etc)
Actual sheet:
Contains the actual dates for the corresponding projected dates.
Results (Days Late):
If you haven't figured it out, this sheet will display the ending results from the two dates. If the date was on-time, it will display "On-Time". And if it was late, it will have this format:
Projected: (Proj. Date)
X Years Y Months Z Days
*The "X Years Y Months Z Days" piece of the formula is just how much time was it late.
This sheet uses an advanced filter to collect Late records under certain headings e.g.:
A2=(Optional)
A3=Sponsor
A4=This_Sponsor
B2=Criteria Heading & Date
B3=This_Heading
B4=*2004*
This example will look for 2004 in cells under "This_Heading" with "This_Sponsor" and show data (if any...obviously)
So now that you have an idea of how my workbook is...Can I make a chart that will use filtered data (visible cells, of course)?
I figure that each heading will have its own graph and the Y-Axis will have an integer value (occurences late given filtered criteria) and linked cells that display what was filtered. This would be an bar graph and the criteria for the formula (for Y data points) would find cells (under the heading) that are NOT blank and do NOT say "On-Time"....a SUMIF function should do the trick.
I'm trying NOT to use VBA for this, BTW. There will be so much data to use that I feel that VBA may slow down calculation significantly.
But what I'm looking to do I have a feeling may not be done so easily...
Here's the premise:
I have 3 sheets: 'Forecast', 'Actual', and 'Results (Days Late)'
Forecast sheet:
Has projected dates for numerous headings (Sample Receipt Date....Repeat Analysis date....etc)
Actual sheet:
Contains the actual dates for the corresponding projected dates.
Results (Days Late):
If you haven't figured it out, this sheet will display the ending results from the two dates. If the date was on-time, it will display "On-Time". And if it was late, it will have this format:
Projected: (Proj. Date)
X Years Y Months Z Days
*The "X Years Y Months Z Days" piece of the formula is just how much time was it late.
This sheet uses an advanced filter to collect Late records under certain headings e.g.:
A2=(Optional)
A3=Sponsor
A4=This_Sponsor
B2=Criteria Heading & Date
B3=This_Heading
B4=*2004*
This example will look for 2004 in cells under "This_Heading" with "This_Sponsor" and show data (if any...obviously)
So now that you have an idea of how my workbook is...Can I make a chart that will use filtered data (visible cells, of course)?
I figure that each heading will have its own graph and the Y-Axis will have an integer value (occurences late given filtered criteria) and linked cells that display what was filtered. This would be an bar graph and the criteria for the formula (for Y data points) would find cells (under the heading) that are NOT blank and do NOT say "On-Time"....a SUMIF function should do the trick.
I'm trying NOT to use VBA for this, BTW. There will be so much data to use that I feel that VBA may slow down calculation significantly.