brotherj74
New Member
- Joined
- Mar 24, 2015
- Messages
- 1
Hello Everyone - I am a newb here and quite the amateur with excel. That being said, I have been able to muddle my way through excel formulas/functions for a spreadsheet I have created to track incidents.
In a nutshell, I have (Sheet1) a column for the date (B4:B100), followed by a column named Incident Category (C4:C100) and then followed by a column named Sub-Incident category (D4:D100). I am using 100 as a range but the number could be more or less depending how many incidents get recorded. Both incident and sub incident columns have associated drop down lists. The value selected from the Incident Category determines the drop down list for Sub-Incident Category. There are other columns (beyond Column D) associated with each incident but they are inconsequential for what I am looking to do.
I am sure there are a ton of more elegant and proper ways to do what I am looking to do, but I was able to copy the contents of the date cells, incident cells and sub-incident cells to another sheet (sheet2) by using the =Sheet1!B4 , =Sheet1!C4 , =Sheet1!D4 formula in the corresponding columns of sheet2 and then copying that formula down to row 100. Whenever I change one of the cell contents I am referring to in Sheet1, the change appears in the appropriate cell in Sheet2.
From there I used =COUNTIF formulas to enumerate matching incident and sub-incidents. I essentially created a table of total (by number) incident count along with each corresponding sub-incident count.
From there I am able to create a nice looking bar chart for metrics.
The problem is, I can't seem to figure out a way to create the chart based on a date range.
So what I would like to achieve, is a formula or vba to replace the simple =Sheet1!B4 etc copy formula with something that would copy data to Sheet2 based upon a date range. That way I could run the chart for 1-month, or 3-months or 1-year etc. Something that checked the date range in column B, and then copied the next two adjacent cells to another sheet (like Sheet2). My COUNTIF logic would still work and subsequently my bar chart would reflect said date range.
Any help is appreciated.
Thank you.
In a nutshell, I have (Sheet1) a column for the date (B4:B100), followed by a column named Incident Category (C4:C100) and then followed by a column named Sub-Incident category (D4:D100). I am using 100 as a range but the number could be more or less depending how many incidents get recorded. Both incident and sub incident columns have associated drop down lists. The value selected from the Incident Category determines the drop down list for Sub-Incident Category. There are other columns (beyond Column D) associated with each incident but they are inconsequential for what I am looking to do.
I am sure there are a ton of more elegant and proper ways to do what I am looking to do, but I was able to copy the contents of the date cells, incident cells and sub-incident cells to another sheet (sheet2) by using the =Sheet1!B4 , =Sheet1!C4 , =Sheet1!D4 formula in the corresponding columns of sheet2 and then copying that formula down to row 100. Whenever I change one of the cell contents I am referring to in Sheet1, the change appears in the appropriate cell in Sheet2.
From there I used =COUNTIF formulas to enumerate matching incident and sub-incidents. I essentially created a table of total (by number) incident count along with each corresponding sub-incident count.
From there I am able to create a nice looking bar chart for metrics.
The problem is, I can't seem to figure out a way to create the chart based on a date range.
So what I would like to achieve, is a formula or vba to replace the simple =Sheet1!B4 etc copy formula with something that would copy data to Sheet2 based upon a date range. That way I could run the chart for 1-month, or 3-months or 1-year etc. Something that checked the date range in column B, and then copied the next two adjacent cells to another sheet (like Sheet2). My COUNTIF logic would still work and subsequently my bar chart would reflect said date range.
Any help is appreciated.
Thank you.
