Compile Data and Produce a Dashboard
- Thread starter Mahendra S
- Start date
Mahendra S
Member
Thanks for re-pro steps dor!.
Luke, Thanks for your responses on enhancements and issues!
Clarification1:
thinking can we generate more appropriate message in-case of err 1004 the err description to asking user to verify the trust center settings with recommendation to (Uncheck the Protected View check boxes and Data Execution Prevention)
Please let me know to have user friendly message possibility here.
Clarification2:
It is good to have protected, but the pop up comes every time.
Is it possible to avoid this popup message and capture below excel message(The first part "The cell or chart that you are trying to change is just read only !") in row25 with red font, please let me know.
Regards,
M
Luke, Thanks for your responses on enhancements and issues!
Clarification1:
thinking can we generate more appropriate message in-case of err 1004 the err description to asking user to verify the trust center settings with recommendation to (Uncheck the Protected View check boxes and Data Execution Prevention)
Please let me know to have user friendly message possibility here.
Clarification2:
It is good to have protected, but the pop up comes every time.
Is it possible to avoid this popup message and capture below excel message(The first part "The cell or chart that you are trying to change is just read only !") in row25 with red font, please let me know.
Regards,
M
Re: Clarification 1
That specific error number is not limited to this problem, so I would hesitate to say that all 1004 errors should produce a single message. As the error only occurs in Protected Mode, I think the better method would be to just tell people that they need to exit protected mode in order to use workbook, or open the file from a network location.
Re: Clarification 2
I'm not sure I understand. The pop-up only appears if you try to edit something. What did you mean by "pop up comes every time"? You could certainly unprotect the sheet, type a message in one of the cells, and then re-protect the sheet, just so people know, but I would expect people should only see the message once, as it tells thm exactly why they can't edit the cells/chart.
That specific error number is not limited to this problem, so I would hesitate to say that all 1004 errors should produce a single message. As the error only occurs in Protected Mode, I think the better method would be to just tell people that they need to exit protected mode in order to use workbook, or open the file from a network location.
Re: Clarification 2
I'm not sure I understand. The pop-up only appears if you try to edit something. What did you mean by "pop up comes every time"? You could certainly unprotect the sheet, type a message in one of the cells, and then re-protect the sheet, just so people know, but I would expect people should only see the message once, as it tells thm exactly why they can't edit the cells/chart.
Mahendra S
Member
Hi Luke, Understood this is more like information and agree with you on clarification1.
Regarding clarification2, to make it clear when ever u try to edit a cell or any content you will get this notification, and it is correct behavior, but the ask is instead of this popup message notification, is it possible to give the message in the row25 from code (not sure this is possible or not)
Regards,
M
Regarding clarification2, to make it clear when ever u try to edit a cell or any content you will get this notification, and it is correct behavior, but the ask is instead of this popup message notification, is it possible to give the message in the row25 from code (not sure this is possible or not)
Regards,
M
Mahendra S
Member
Hi Luke, I hope you had good weekend!
Please see the latest template which i have modified no more to get the message.
Basically added the code cancel = true for Worksheet_BeforeDoubleClick and Worksheet_SelectionChange.
This resolved the additional notification and no need for row25 message.
A few enhancements I would like to discuss with you.
Requirement1:
Part1:When user clicks on Copy Rows button, need to disable the "Copy Rows" button and "Transfer to Defects" button as soon as user clicks on Copy Rows button. Once the process completes then enable the buttons first Copy Rows and next Transfer to Defects buttons.
Part2: Need to create a report upon successful completion of every time the successful completion of Copy Rows event, the transaction data (Snapshot Id, Snapshot Complete Date, Snapshot Complete Time, Total Snapshot Records) need to stored in Snapshot History tab.
For example the copy rows completed for 50 records in MasterData tab, snapshot history tab should be updated
[1, 03/24/2014, 6 AM, 50] in respective columns.
whenever the next time if user performs next copy rows it should insert in to next row.
Please let me know the implementation possibility of above two parts.
Requirement2: Need to Protect and Un protect the Master Data dynamically.
The Master Data tab related data always need to be readonly possibly, since this is master data user should not tamper/play with it.
However the protection and read-only data needs to be changed in to un protection and write mode dynamically as soon as copy rows button clicked and till execution completes. Once the copy rows operation completes the Master Data tab need to be read only and protected.
This will give complete control on Master Data.
Please let me know implementation possibilities on above req's
Regards,
M
Please see the latest template which i have modified no more to get the message.
Basically added the code cancel = true for Worksheet_BeforeDoubleClick and Worksheet_SelectionChange.
This resolved the additional notification and no need for row25 message.
A few enhancements I would like to discuss with you.
Requirement1:
Part1:When user clicks on Copy Rows button, need to disable the "Copy Rows" button and "Transfer to Defects" button as soon as user clicks on Copy Rows button. Once the process completes then enable the buttons first Copy Rows and next Transfer to Defects buttons.
Part2: Need to create a report upon successful completion of every time the successful completion of Copy Rows event, the transaction data (Snapshot Id, Snapshot Complete Date, Snapshot Complete Time, Total Snapshot Records) need to stored in Snapshot History tab.
For example the copy rows completed for 50 records in MasterData tab, snapshot history tab should be updated
[1, 03/24/2014, 6 AM, 50] in respective columns.
whenever the next time if user performs next copy rows it should insert in to next row.
Please let me know the implementation possibility of above two parts.
Requirement2: Need to Protect and Un protect the Master Data dynamically.
The Master Data tab related data always need to be readonly possibly, since this is master data user should not tamper/play with it.
However the protection and read-only data needs to be changed in to un protection and write mode dynamically as soon as copy rows button clicked and till execution completes. Once the copy rows operation completes the Master Data tab need to be read only and protected.
This will give complete control on Master Data.
Please let me know implementation possibilities on above req's
Regards,
M
Re: Part 1
I'm not sure what you want done. While macro is running, user can't click on anything, so why do we need to disable the buttons?
Re: Part 2
UpdateSnapshot macro written.
Re: Requirement 2
This is fairly easy to implement. Added a few lines to the CopyRows macro, e.g.
I'm not sure what you want done. While macro is running, user can't click on anything, so why do we need to disable the buttons?
Re: Part 2
UpdateSnapshot macro written.
Re: Requirement 2
This is fairly easy to implement. Added a few lines to the CopyRows macro, e.g.
Code:
Worksheets("MasterData").ProtectMahendra S
Member
Hi Luke, Looks like snapshot has good procedure now, however when I ran Copy Rows it is throwing following compile err, looks like mismatch with proc paramaters or something else, can you please check.
It is getting halted at Call UpdateSnapshot(DataRows)
Regards,
M
It is getting halted at Call UpdateSnapshot(DataRows)
Regards,
M
Mahendra S
Member
Thanks Luke, thought to remove the arguments for snapshot proc, but want to check with you before. I think snapshot is working perfect.
Will let you know if any other issues.
Regards,
M
Will let you know if any other issues.
Regards,
M
Mahendra S
Member
Hi Luke, We are almost on final enhancements hopefully for this phase.
One required enhancement as below.
Enhancement1: I will have QAT, UAT tabs (example is "MS QAT Defects") which will contain Priority column with different data set (1-Critical, 2-Severe, 3-Average, 4-Low).
The enhancement required is need to convert if status has above data set it should change dynamically to (Critical, High, Medium, Low).
I would keep the source data same what ever it comes as (1-Critical, 2-Severe, 3-Average, 4-Low).
During the copy rows proc execution or after copy rows execution,can we have check to change the status like "1-Critical" need to convert to "Critical", "2-Severe" need to convert to "High", "3-Average" need to convert to "Medium" and "4-Low" need to convert to "Low".
This check need to happen to make sure the data correctly reflects.
Enhancement2: Similar to Priority column, for Status column I might have "New" as one of status and this might come from some tabs, any time if I have status as "New" that need to be changed to "Open" in Master Data (Status column)
These two enhancements need to happen in Master Data Tab validation, please advise optimistic approach for these let me know with possible solution.
Enhancement3: I have added two more statuses, Returned and Postponed, however once we have above validations in place need to make sure Record Count matches as below.
Dashboard Records{ALL} = Master Data Record Count = Snapshot Record Count
This rule will provide tight data integrity between the worksheets.
Attaching the latest template with additional data for your ref.
Regards,
M
One required enhancement as below.
Enhancement1: I will have QAT, UAT tabs (example is "MS QAT Defects") which will contain Priority column with different data set (1-Critical, 2-Severe, 3-Average, 4-Low).
The enhancement required is need to convert if status has above data set it should change dynamically to (Critical, High, Medium, Low).
I would keep the source data same what ever it comes as (1-Critical, 2-Severe, 3-Average, 4-Low).
During the copy rows proc execution or after copy rows execution,can we have check to change the status like "1-Critical" need to convert to "Critical", "2-Severe" need to convert to "High", "3-Average" need to convert to "Medium" and "4-Low" need to convert to "Low".
This check need to happen to make sure the data correctly reflects.
Enhancement2: Similar to Priority column, for Status column I might have "New" as one of status and this might come from some tabs, any time if I have status as "New" that need to be changed to "Open" in Master Data (Status column)
These two enhancements need to happen in Master Data Tab validation, please advise optimistic approach for these let me know with possible solution.
Enhancement3: I have added two more statuses, Returned and Postponed, however once we have above validations in place need to make sure Record Count matches as below.
Dashboard Records{ALL} = Master Data Record Count = Snapshot Record Count
This rule will provide tight data integrity between the worksheets.
Attaching the latest template with additional data for your ref.
Regards,
M
Not a problem. 
Re: Enchancement 1 & 2
The DateCheck macro has been edited to include several "Replace" commands, switching labels in Priority and Status column.
Re: Enchancement 3
The SUM formulas in the Total cells needed to have their ranges adjusted. All sheets now show 54 records
Re: Enchancement 1 & 2
The DateCheck macro has been edited to include several "Replace" commands, switching labels in Priority and Status column.
Re: Enchancement 3
The SUM formulas in the Total cells needed to have their ranges adjusted. All sheets now show 54 records
Mahendra S
Member
Hi Luke, Thank you very much for enhancing the priority and status columns, this is working fantastic, will need to load more data to see the charts how they reflects. Also thanks for adjusting the total formula's for dashboard.
Will get back later if I see any other issues.
Have a great day!
Regards,
M
Will get back later if I see any other issues.
Have a great day!
Regards,
M
Mahendra S
Member
Hi Luke,
I am seeing an Type mismatch err in FilterRows proc as below
Copyrows is working fine, when I click Transfer to it is throwing above
Please let me know anything to change in data type definitions in Filter Proc
Also the dashboard 2nd filter results are not matching., not sure where the issue is.
But above err is not a good one unfortunately, please help me if u can.
Regards,
M
I am seeing an Type mismatch err in FilterRows proc as below
Copyrows is working fine, when I click Transfer to it is throwing above
Please let me know anything to change in data type definitions in Filter Proc
Also the dashboard 2nd filter results are not matching., not sure where the issue is.
But above err is not a good one unfortunately, please help me if u can.
Regards,
M
Mahendra S
Member
Hi Luke, after further investigation I found the issue is with Date field, in source data (ex I have added in Oracle Prod Defects tab, ETA To Fix as TBD, No ETA for two rows) please see the attached template.
I believe we can fix this. Is it possible to do another check if a row has non date related data like above simply change that specific Date value to No ETA with Red color and Yellow text as current rule we have which is if no data in the date filed of ETA to Fix column in Defects Priority.
Need your suggestion.
Issue2: Dashboard Group Filter2 (Medium and Low) is still not matching with Source Filter1, please double check on this.
Regards,
M
I believe we can fix this. Is it possible to do another check if a row has non date related data like above simply change that specific Date value to No ETA with Red color and Yellow text as current rule we have which is if no data in the date filed of ETA to Fix column in Defects Priority.
Need your suggestion.
Issue2: Dashboard Group Filter2 (Medium and Low) is still not matching with Source Filter1, please double check on this.
Regards,
M
Mahendra S
Member
Hi Luke, I think the error handler really helped to nail down this issue with Type Mismatch.
But please let me know on below enhancement.
Enhancement1:
Is it possible to do another check if a row has non date related data(any text other than date) like above simply change that specific non Date value to No ETA with Red color and Yellow text.
Appreciate your response.
Regards,
But please let me know on below enhancement.
Enhancement1:
Is it possible to do another check if a row has non date related data(any text other than date) like above simply change that specific non Date value to No ETA with Red color and Yellow text.
Appreciate your response.
Regards,
Mahendra S
Member
Hi Luke, Please let me know solution for above enhancement 1 to resolve the type mismatch issue.
Even though the data is getting transfered and filtered through at the end it is throwing Type mismatch err in FilterRows.
Appreciate if you can take a look and let me know.
Regards,
M
Even though the data is getting transfered and filtered through at the end it is throwing Type mismatch err in FilterRows.
Appreciate if you can take a look and let me know.
Regards,
M
Re: Enchancement2
In the FilterRows macro, I've added a new group of ETAtext to format all the cells with non-date entries. The 3 groups now are Blank cells, cells with dates, and cells with text. The current sample book has all expired dates, so everything is formatted. Question, the blank cells were previously red with white text, but now you said yellow text. Did you want white or yellow? I've changed to yellow for now, but you can easily change modifying the RGB callout in the code. Yellow is 255,255,0 and white is 255,255,255
Re: Issue2
I'm not seeing the issue...can you clarify (if this still exists).
@NARAYANK991
Thanks for all the "likes" my friend.
In the FilterRows macro, I've added a new group of ETAtext to format all the cells with non-date entries. The 3 groups now are Blank cells, cells with dates, and cells with text. The current sample book has all expired dates, so everything is formatted. Question, the blank cells were previously red with white text, but now you said yellow text. Did you want white or yellow? I've changed to yellow for now, but you can easily change modifying the RGB callout in the code. Yellow is 255,255,0 and white is 255,255,255
Re: Issue2
I'm not seeing the issue...can you clarify (if this still exists).
@NARAYANK991
Thanks for all the "likes" my friend.
NARAYANK991
Excel Ninja
Hi Luke ,
After a long time , I am seeing a thread which looks like a software project ! It is good that Mahendra is a great communicator. I admire your loyalty ! and of course , your 'Excel'lence.
Narayan
After a long time , I am seeing a thread which looks like a software project ! It is good that Mahendra is a great communicator. I admire your loyalty ! and of course , your 'Excel'lence.
Narayan
Mahendra S
Member
Hi Narayan, Thanks for all appreciation and credit goes to Luke for his all time great support on this thread!
Hi Luke, Issue2 is not re-proable every time but this needs to be evaluated as I am certain with mismatch in prev templates.
I would like to bring few admin scenario's to your attention.
Original Filter Scenario1: If I add new Group for ex: "QC" in Group table in Super Admin tab then it should reflect in DefectsPriority Tab's 'Select By Assigned' filter with QC and Dashboard tab Group filter with QC , ideally this would be the filter behavior.
But currently the data in DefectsPriority tab 'Select By Assigned' filter data and Dashboard tab Group filters are getting populated based on data from MasterData or Source Tabs.
Impact: From users perspective this is still a missing filter value issue.
Is it possible to go with Original Filter Scenario1, please let me know.
I will write about a detailed summary report requirement in next thread, I believe this would be simple to generate as we already had most of parameters, but let us evaluate if you are ok.
Regards,
M
Hi Luke, Issue2 is not re-proable every time but this needs to be evaluated as I am certain with mismatch in prev templates.
I would like to bring few admin scenario's to your attention.
Original Filter Scenario1: If I add new Group for ex: "QC" in Group table in Super Admin tab then it should reflect in DefectsPriority Tab's 'Select By Assigned' filter with QC and Dashboard tab Group filter with QC , ideally this would be the filter behavior.
But currently the data in DefectsPriority tab 'Select By Assigned' filter data and Dashboard tab Group filters are getting populated based on data from MasterData or Source Tabs.
Impact: From users perspective this is still a missing filter value issue.
Is it possible to go with Original Filter Scenario1, please let me know.
I will write about a detailed summary report requirement in next thread, I believe this would be simple to generate as we already had most of parameters, but let us evaluate if you are ok.
Regards,
M
Mahendra S
Member
Luke, Attaching the template for your reference.
Thank you!
Regards,
M
Thank you!
Regards,
M
Thanks for the template. I see what you mean about the dropdowns, I forgot that the Group/Assigned dropdowns were the odd one out. I've changed it so that they are populated based on the Super Admin. Note that for all the dropdowns, you don't have to include "ALL" in the list, the code automatically adds it in. There is a new button on Super Admin to update the Dropdowns, in case someone updates a list and wants immediate update, rather than doing another Filter/Copy rows.
I also noticed that on the Dashboard, the 2nd chart was not correctly picking up the Returned and Postponed groups, so I fixed that.
I also noticed that on the Dashboard, the 2nd chart was not correctly picking up the Returned and Postponed groups, so I fixed that.
Mahendra S
Member
Hi Luke, Thanks for all the drop down fixes.
I see one record missing between dashboard(All) and Master Data, I will troubleshoot on this. However I see great user experience with template powerful dynamic updates!
Dashboard Requirement1: I would like to discuss here the advanced dashboard( basically combination of both dashboard1+dashboard2)
the row 29 in dashboard tab should be shown with two filters (Select By Source: Dropdown; Select By Group Dropdown)
remaining rows should be same as dashboard1 and dashboard2 except data numbers will vary based on filters.
The chart title should be based on combination selected some thing like
Not closed defects for Source + Group : <dropdownval1 + dropdownval2>
Please let me know on above dashboard report design and possible solution
If above report is not possible what are other options please let me know.
I would think Pivot report but need your input and which ever easy to implement.
If we are going go with Pivot possibly in different tab.
Template would be the same if you would like to see.
I haven't changed anything much in y'day your version.
Thanks again,
Regards,
M
I see one record missing between dashboard(All) and Master Data, I will troubleshoot on this. However I see great user experience with template powerful dynamic updates!
Dashboard Requirement1: I would like to discuss here the advanced dashboard( basically combination of both dashboard1+dashboard2)
the row 29 in dashboard tab should be shown with two filters (Select By Source: Dropdown; Select By Group Dropdown)
remaining rows should be same as dashboard1 and dashboard2 except data numbers will vary based on filters.
The chart title should be based on combination selected some thing like
Not closed defects for Source + Group : <dropdownval1 + dropdownval2>
Please let me know on above dashboard report design and possible solution
If above report is not possible what are other options please let me know.
I would think Pivot report but need your input and which ever easy to implement.
If we are going go with Pivot possibly in different tab.
Template would be the same if you would like to see.
I haven't changed anything much in y'day your version.
Thanks again,
Regards,
M
ashish mehra
Active Member
Yes Narayan Sir,
I am sure there are many Silent followers of this thread.
@r1c1 Chandoo
: Can you please make this thread & include as a Case Study in your words in Dashboard Course? Why not invite Luke as a Guest Excel Ninza?Luke: Can you please tag Chandoo ! (Edit by Luke M: DONE!
)Regards,
AM
Last edited by a moderator:
Re: Discrepency between Master Data and dashboard
In the file I uploaded, the Dashboard is showing 55 total, and the Master Data has 55 rows of data (don't forget to not count the header row). If you can find another example where a discrepency exists, let me know, and I will investigate.
Re: Dashboard Requirement
I've created a 3rd table, which is a combination of the previous 2. I wasn't sure if you wanted to have different dropdowns, but I assumed not, so it's based off of the already existing dropdowns. New chart has also been created. Dynamic labels created. I had to adjust some of the chart sizing to make things fit. I don't think a PivotTable is necessary at this point, as the COUNTIFS function works very nicely.
-------
@Ashish Mishra and @NARAYANK991
I'm glad this thread has been helpful to others. It's certainly been one of the longer projects I've worked on here at the forums, and it's been neat to see how the workbook develops. Credit to @Mahendra S for consistently providing example workbooks and clear requirements on what needs to be done.
@r1c1
Not sure how much "polishing up" might be needed on this thread, but I can see that it would be a good example to show how dashboard projects develop, and/or how to work with a customer type relation to build a tool. I'm always glad to help, so please let me know what you think.
In the file I uploaded, the Dashboard is showing 55 total, and the Master Data has 55 rows of data (don't forget to not count the header row). If you can find another example where a discrepency exists, let me know, and I will investigate.
Re: Dashboard Requirement
I've created a 3rd table, which is a combination of the previous 2. I wasn't sure if you wanted to have different dropdowns, but I assumed not, so it's based off of the already existing dropdowns. New chart has also been created. Dynamic labels created. I had to adjust some of the chart sizing to make things fit. I don't think a PivotTable is necessary at this point, as the COUNTIFS function works very nicely.
-------
@Ashish Mishra and @NARAYANK991
I'm glad this thread has been helpful to others. It's certainly been one of the longer projects I've worked on here at the forums, and it's been neat to see how the workbook develops. Credit to @Mahendra S for consistently providing example workbooks and clear requirements on what needs to be done.
@r1c1
Not sure how much "polishing up" might be needed on this thread, but I can see that it would be a good example to show how dashboard projects develop, and/or how to work with a customer type relation to build a tool. I'm always glad to help, so please let me know what you think.