Total number of Report in a month to display
- Thread starter webmax
- Start date
Hi Sham,
The first part of getting the totals is pretty easy. Put this formula in B14, copy down and across.
=COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"*"&B$13&"*")+COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"Monthly")+COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"Quarterly")*OR(B$13={"Mar","Jun","Sep","Dec"})
Your request to get the Report names in yellow cells is much more complicated. To change what cells say based on selected cell will require VB. Macro would need to loop over all the data cells, build matrix of months/report names, and then output to yellow cells. To be helpful, will need to have this run fairly quickly. Do you still want to pursue this route? Also, how big will the input data potentially be? Is example sheet a good representation, or is actual data much larger?
The first part of getting the totals is pretty easy. Put this formula in B14, copy down and across.
=COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"*"&B$13&"*")+COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"Monthly")+COUNTIFS($A$3:$A$7,$A14,$C$3:$C$7,"Quarterly")*OR(B$13={"Mar","Jun","Sep","Dec"})
Your request to get the Report names in yellow cells is much more complicated. To change what cells say based on selected cell will require VB. Macro would need to loop over all the data cells, build matrix of months/report names, and then output to yellow cells. To be helpful, will need to have this run fairly quickly. Do you still want to pursue this route? Also, how big will the input data potentially be? Is example sheet a good representation, or is actual data much larger?
Macros written in the sheet module to trigger a selection event. Main macro is stored in Module1. Please read through the code and comments to see how to change if your actual data is different size/location.
Macro currently set to run when you select a cell in range B14:M16
Macro currently set to run when you select a cell in range B14:M16
Last edited:
Hi:
When you reference Range like this:
it's called an unqualified reference because you don't specifically say which sheet the range is on
Try changing your code to
The code will work fine.
Thanks
When you reference Range like this:
Code:
'Where is source data?
Set rngData = Range("A3:C7")
'Where is start of yellow cells?
Set rngStart =Range("B18")Try changing your code to
Code:
'Where is source data?
Set rngData = Sheet1.Range("A3:C7")
'Where is start of yellow cells?
Set rngStart = Sheet1.Range("B18")The code will work fine.
Thanks