Compile Data and Produce a Dashboard
- Thread starter Mahendra S
- Start date
SirJB7
Excel Rōnin
@r1c1
Hi, bwana!
If I were you I'd check if XenForo software handles more than 1000 posts at a single thread... just in case.
Regards!
@Luke M
Hi, buddy!
Will be the 1st member at chandoo.org's forums with a thread that reaches it 1st posts' milestone?
Regards!
PS: Should we open the category Ninja Topic?
Hi, bwana!
If I were you I'd check if XenForo software handles more than 1000 posts at a single thread... just in case.
Regards!
@Luke M
Hi, buddy!
Will be the 1st member at chandoo.org's forums with a thread that reaches it 1st posts' milestone?
Regards!
PS: Should we open the category Ninja Topic?
Mahendra S
Member
Thanks Luke, the template is good now.
One enhancement I would like to discuss, as this has some dependency.
Enhancement:
Is it possible to have seperate proc/fun to verify the Group Table values from Super Admin with all source tabs that has Assigned Team column.
Basically I would like to have cross check between SuperAdmin & All source Tabs for Group Table (Super Admin) and Assigned Team.
If we are missing any unique record in Group Table we should be able to just add to Group Table (Super Admin).
If we have more values in Group table we are fine, only if we have less values in the Group table compared with other tabs then we need to add.
Currently this manual, but good to have cross reference.
Probably another button "Group/Assigned Team Sync" in Super Admin to verify the source tabs.
Please let me know on this.
Regards,
M
One enhancement I would like to discuss, as this has some dependency.
Enhancement:
Is it possible to have seperate proc/fun to verify the Group Table values from Super Admin with all source tabs that has Assigned Team column.
Basically I would like to have cross check between SuperAdmin & All source Tabs for Group Table (Super Admin) and Assigned Team.
If we are missing any unique record in Group Table we should be able to just add to Group Table (Super Admin).
If we have more values in Group table we are fine, only if we have less values in the Group table compared with other tabs then we need to add.
Currently this manual, but good to have cross reference.
Probably another button "Group/Assigned Team Sync" in Super Admin to verify the source tabs.
Please let me know on this.
Regards,
M
Mahendra S
Member
Hi Luke,
Apart from above enhancement, I am seeing below err, looks like we need to add proper err handler to missing procedures and one of them as below with
Private Sub Worksheet_Change(ByVal Target As Range)
The err I am seeing is as below
When you try to click on any cell to edit in Defects Priority and enter text and then click on below cell / or any other cell it is throwing err. The color update is not functioning.
Please let me know if we are missing something in the code and double verify for all error handlers in each and every proc.
Regards,
M
Apart from above enhancement, I am seeing below err, looks like we need to add proper err handler to missing procedures and one of them as below with
Private Sub Worksheet_Change(ByVal Target As Range)
The err I am seeing is as below
When you try to click on any cell to edit in Defects Priority and enter text and then click on below cell / or any other cell it is throwing err. The color update is not functioning.
Please let me know if we are missing something in the code and double verify for all error handlers in each and every proc.
Regards,
M
Re: Enhancement
New Button and macro created to check sheet list. If Sheet is added, a MsgBox pop-ups, just to make sure user notices that something has been added to the table.
Re: Error
Looks like when I was replacing all the "c" variables, I accidentally changed the C:C range reference. Code has been corrected, and error handling as been added.
Re: @SirJB7
I don't think this has become the longest thread in our forum...but it's getting close. I've seen other forums with much longer threads, though. This one is certainly the longest project I've been on, but it's been enjoyable.
New Button and macro created to check sheet list. If Sheet is added, a MsgBox pop-ups, just to make sure user notices that something has been added to the table.
Re: Error
Looks like when I was replacing all the "c" variables, I accidentally changed the C:C range reference. Code has been corrected, and error handling as been added.
Re: @SirJB7
I don't think this has become the longest thread in our forum...but it's getting close. I've seen other forums with much longer threads, though. This one is certainly the longest project I've been on, but it's been enjoyable.
Mahendra S
Member
Thanks for the update Luke.
I have updated the template for your reference.
I have re arranged the button and placed accordingly in super admin.
The earlier ask for Group/Team sync is different enhancement.
Enhancement1(Worksheet Sync): Regarding this enhancement I have renamed button to more meaningful as 'Worksheet Sync"
Few things we need to take care.
A validation need to be performed prior running copy rows from Publish button.
Need to check the worksheet table if you have all columns filled or not.
Worksheet mandatory table rule: The proc should only run when you have "Publish", "WS Name", "WS Code", "Env" are all filled worksheet table.
If any row that dont have above four column details it should not run and skip to next row.
Also currently seeing below err when you add new worksheet and Publish -> Yes
I believe we need to add err handler if not added to the new macro associated with worksheet sync.
(I haven't looked at code yet)
I think once we have above worksheet mandatory table rule implemented we will not see this issue.
But it is better to have err handler for every macro/proc we do write.
Please let me know the feasibility on above need.
Enhancement2 (Group/Team Sync):
I have placed the button "Group/Team Sync" in cell J1 in super admin.
This enhancement is to verify the Group Table values (Super Admin) with all source tabs that has Assigned Team column unique values.
Basically I would like to have cross check between SuperAdmin (Group table) & All source Tabs for Assigned Team columnJ
(For example pl see ETR Prod Defects tab with column J where we have new value "ETR-SA" in Assigned Team column J at cell 62 and 64 )
we are missing this ETR-SA unique value in Super Admin (Group Table), we should be able to just add to Group Table dynamically by clicking button "Group/Team Sync"
If we have more values in Group table we are fine, only if we have less values in the Group table compared with other tabs then we need to add.
Please let me know if we are in same understanding with Enhancement2 (Group/Team Sync)
Thank you very much and I appreciate all the great work.
Regards,
M
I have updated the template for your reference.
I have re arranged the button and placed accordingly in super admin.
The earlier ask for Group/Team sync is different enhancement.
Enhancement1(Worksheet Sync): Regarding this enhancement I have renamed button to more meaningful as 'Worksheet Sync"
Few things we need to take care.
A validation need to be performed prior running copy rows from Publish button.
Need to check the worksheet table if you have all columns filled or not.
Worksheet mandatory table rule: The proc should only run when you have "Publish", "WS Name", "WS Code", "Env" are all filled worksheet table.
If any row that dont have above four column details it should not run and skip to next row.
Also currently seeing below err when you add new worksheet and Publish -> Yes
I believe we need to add err handler if not added to the new macro associated with worksheet sync.
(I haven't looked at code yet)
I think once we have above worksheet mandatory table rule implemented we will not see this issue.
But it is better to have err handler for every macro/proc we do write.
Please let me know the feasibility on above need.
Enhancement2 (Group/Team Sync):
I have placed the button "Group/Team Sync" in cell J1 in super admin.
This enhancement is to verify the Group Table values (Super Admin) with all source tabs that has Assigned Team column unique values.
Basically I would like to have cross check between SuperAdmin (Group table) & All source Tabs for Assigned Team columnJ
(For example pl see ETR Prod Defects tab with column J where we have new value "ETR-SA" in Assigned Team column J at cell 62 and 64 )
we are missing this ETR-SA unique value in Super Admin (Group Table), we should be able to just add to Group Table dynamically by clicking button "Group/Team Sync"
If we have more values in Group table we are fine, only if we have less values in the Group table compared with other tabs then we need to add.
Please let me know if we are in same understanding with Enhancement2 (Group/Team Sync)
Thank you very much and I appreciate all the great work.
Regards,
M
CopyRows now performs WS Synch check first. If sheets added, or there are blank cells in the SheetTable, performs a hard stop. Err handler seems to be working now.
Group Sync also been created. This does not result in a hard stop, as code adds the Group name for you, and then proceeds to filter. A msg is displayed if name is added, just as an FYI to user. Slight Change to stated requirement: You originally wrote that Sync would look at all the different tabs. How I wrote the code, it's actually looking at MasterData tab. This is far easier, as we don't have to figure out which sheets to look at, and all the data is in one place. In the CopyRows macro, the Sync is set to run after MasterData is populated. If this is not acceptable for some reason, please let me know.
I moved both macros to a separate module, as part of better coding practice. I felt they were getting large enough that they shouldn't be "hidden" in a Worksheet module.
Group Sync also been created. This does not result in a hard stop, as code adds the Group name for you, and then proceeds to filter. A msg is displayed if name is added, just as an FYI to user. Slight Change to stated requirement: You originally wrote that Sync would look at all the different tabs. How I wrote the code, it's actually looking at MasterData tab. This is far easier, as we don't have to figure out which sheets to look at, and all the data is in one place. In the CopyRows macro, the Sync is set to run after MasterData is populated. If this is not acceptable for some reason, please let me know.
I moved both macros to a separate module, as part of better coding practice. I felt they were getting large enough that they shouldn't be "hidden" in a Worksheet module.
It's a step-by-step (by step by step by step...) story of how to build a dashboard with lots of different functionality.Just curious to know..
is this a thread or a sub-forum...
Mahendra S
Member
Hi Luke, Thanks for the updates.
The Proc should run if we have completed values in the worksheet table for any row, please see second part of the rule which I would like to restate as If any row that dont have above four column details in worksheet table CopyRows should skip to next row and execute remaining and complete.
At the end we might throw a message to say the specific WS Nmae has blanlk values
(Worksheet mandatory table rule: The proc should only run when you have "Publish", "WS Name", "WS Code", "Env" are all filled worksheet table.
Second part: If any row that dont have above four column details it should not run and skip to next row.)
Please let me know is this works for you.
Regarding GroupSync to look in to MasterData that is fine and I agree this is much better design. but need to see if any edge case impacts will let you know.
Regards,
M
The Proc should run if we have completed values in the worksheet table for any row, please see second part of the rule which I would like to restate as If any row that dont have above four column details in worksheet table CopyRows should skip to next row and execute remaining and complete.
At the end we might throw a message to say the specific WS Nmae has blanlk values
(Worksheet mandatory table rule: The proc should only run when you have "Publish", "WS Name", "WS Code", "Env" are all filled worksheet table.
Second part: If any row that dont have above four column details it should not run and skip to next row.)
Please let me know is this works for you.
Regarding GroupSync to look in to MasterData that is fine and I agree this is much better design. but need to see if any edge case impacts will let you know.
Regards,
M
Mahendra S
Member
Hi Luke, Just a quick update on Group/Team Sync.
when you run copyrows at the end of the proc, it is getting updated to Group table in super admin.
The issue we will have is Dashboard tab filters will not get updated.
A quick check is change a Team value in ETR Tab and Publish and see what happens with current implementation.
Dashboard Select By Group is not updated/refreshed.
Also if we are modifying/removing any value it will reflect in MasterData, however the same is not getting reflected in superadmin Group table.
Let me know if you need sample example on this.
Thanks,
M
when you run copyrows at the end of the proc, it is getting updated to Group table in super admin.
The issue we will have is Dashboard tab filters will not get updated.
A quick check is change a Team value in ETR Tab and Publish and see what happens with current implementation.
Dashboard Select By Group is not updated/refreshed.
Also if we are modifying/removing any value it will reflect in MasterData, however the same is not getting reflected in superadmin Group table.
Let me know if you need sample example on this.
Thanks,
M
Mahendra S
Member
Hi Luke,
Few updates based on my observation with latest template.
Enhancement1: Publish DefectSet enable and execute based on following criteria.
In worksheet table
If Publish = Yes and remaining three columns (WS Name, WS Code, Env) filled with values then the proc runs.
For example if first row has Yes and all remaining three columns has data then it will run thru.
next record if you have Publish = No, the execution need to skip and verify next row and so on.
At the end of copy rows execution, if we have any records with Publish = "No" or Publish = empty and any other cell values are empty
the message need to display the corresponding <WS Names> has incomplete data from worksheet table.
Enhancement2: 'Group/Team Sync' Mismatch conditions:
We need to run the Group/Team Sync macro at the end of Master data worksheet population and make sure the Dashboard second filter (Select By Group filter ) and DefectsPriority (Select By Team filter) need to be updated accordingly if there is a new value or if any value that is modified.
Also the SuperAdmin Group table should reflect the same unique values which we have from master data.
This way we will have tie between SuperAdmin and Dashboard and DefectsPririty
If we have additional values in SuperAdmin (Group table) after running the proc automatically the additional records need to be deleted to have sync.
(if this is not possible we can live as-is, but good to have clear sync)
Please let me know your thoughts on this.
Issue1: Currently I am seeing this issue in Defects Priority worksheet.
If we double click on status cells/priority cells the color is getting changed.
We need to make sure only if there is change in text then it should get highlighted. otherwise this is not giving value what we intended for. Please let me know how to fix this issue.
Issue2: Without entering any data in Progress/Resolution if you double click then the text is getting duplicated.
We need to make sure the new text entered is always need to be appended to old text, otherwise this will not give value. double click append is not correct.
This is important for us to track the old content and add next content to old content always.
Please let me know if we can resolve this.
Attaching the latest template for your ref.
I appreciate all the support.
Thank you.
Regards,
M
Few updates based on my observation with latest template.
Enhancement1: Publish DefectSet enable and execute based on following criteria.
In worksheet table
If Publish = Yes and remaining three columns (WS Name, WS Code, Env) filled with values then the proc runs.
For example if first row has Yes and all remaining three columns has data then it will run thru.
next record if you have Publish = No, the execution need to skip and verify next row and so on.
At the end of copy rows execution, if we have any records with Publish = "No" or Publish = empty and any other cell values are empty
the message need to display the corresponding <WS Names> has incomplete data from worksheet table.
Enhancement2: 'Group/Team Sync' Mismatch conditions:
We need to run the Group/Team Sync macro at the end of Master data worksheet population and make sure the Dashboard second filter (Select By Group filter ) and DefectsPriority (Select By Team filter) need to be updated accordingly if there is a new value or if any value that is modified.
Also the SuperAdmin Group table should reflect the same unique values which we have from master data.
This way we will have tie between SuperAdmin and Dashboard and DefectsPririty
If we have additional values in SuperAdmin (Group table) after running the proc automatically the additional records need to be deleted to have sync.
(if this is not possible we can live as-is, but good to have clear sync)
Please let me know your thoughts on this.
Issue1: Currently I am seeing this issue in Defects Priority worksheet.
If we double click on status cells/priority cells the color is getting changed.
We need to make sure only if there is change in text then it should get highlighted. otherwise this is not giving value what we intended for. Please let me know how to fix this issue.
Issue2: Without entering any data in Progress/Resolution if you double click then the text is getting duplicated.
We need to make sure the new text entered is always need to be appended to old text, otherwise this will not give value. double click append is not correct.
This is important for us to track the old content and add next content to old content always.
Please let me know if we can resolve this.
Attaching the latest template for your ref.
I appreciate all the support.
Thank you.
Regards,
M
Double-click errors
This one is tricky, as when you start to edit a cell (regular or double-click), and then press anything except "Esc" to leave the cell, XL assumes there was a change. Trying to detect whether there truly was a change or not gets exteremly tricky, as cells don't have memory of previous value, so you have to undo, store value, compare. This wouldn't work with our Append method. So I was stuck for a bit.
My proposal (seen in attached) is to disable the double-click. User can still directly type into a cell to make changes, which prevents someone from trying to be helping and adding to end of Progress cell, when in reality we want to append via macro. The BeforeDoubleClick event that I wrote disables the double-click, but insteads increases/decreases row height so that user can read all contents of a cell. This is a bit of a throwback to our text box idea. If there is another reason for the double-click, I will attempt to think of a new work-around, but let me know what you think.
Group Sync
Macro now makes sure that everything that's listed in MasterData is in SuperAdmin and vice versa. The GroupSync macro now Updates all the dropdowns, so these should always be synced. I think I've got this fixed now, but please let me know if I missed something in your description of this error.
Missing Data
Macro now goes to next sheet if missing data for that row. Displays error message at end. If not data is copied to Master Data (through errors or some other reason), user is notifed, and Code stops before filtering to DefectsFilter. This prevents table on DefectsFilter from getting completely deleted. User should fix error, and then run macro again.
This one is tricky, as when you start to edit a cell (regular or double-click), and then press anything except "Esc" to leave the cell, XL assumes there was a change. Trying to detect whether there truly was a change or not gets exteremly tricky, as cells don't have memory of previous value, so you have to undo, store value, compare. This wouldn't work with our Append method. So I was stuck for a bit.
My proposal (seen in attached) is to disable the double-click. User can still directly type into a cell to make changes, which prevents someone from trying to be helping and adding to end of Progress cell, when in reality we want to append via macro. The BeforeDoubleClick event that I wrote disables the double-click, but insteads increases/decreases row height so that user can read all contents of a cell. This is a bit of a throwback to our text box idea. If there is another reason for the double-click, I will attempt to think of a new work-around, but let me know what you think.
Group Sync
Macro now makes sure that everything that's listed in MasterData is in SuperAdmin and vice versa. The GroupSync macro now Updates all the dropdowns, so these should always be synced. I think I've got this fixed now, but please let me know if I missed something in your description of this error.
Missing Data
Macro now goes to next sheet if missing data for that row. Displays error message at end. If not data is copied to Master Data (through errors or some other reason), user is notifed, and Code stops before filtering to DefectsFilter. This prevents table on DefectsFilter from getting completely deleted. User should fix error, and then run macro again.
Mahendra S
Member
Thanks Luke, very nice to see the Group Sync, good work!!
Double click conditions: Need to do some additional validation and research on double click as there are some dependencies and edge case scenario's. will verify at eod today.
However I would like to bring one more enhancement on WS Code <-> Select By Source (Dashboard, DefectsPriority)
Enhancement3:
We might need to run a macro to support WS Code/Select By Source to be in sync at the end of Master data worksheet population and make sure the Dashboard first filter (Select By Source filter ) and DefectsPriority (Select By Source filter) need to be updated accordingly based on superadmin worksheet table Publish = "Yes" criteria.
Please let me know your thoughts on this.
Regards,
M
Double click conditions: Need to do some additional validation and research on double click as there are some dependencies and edge case scenario's. will verify at eod today.
However I would like to bring one more enhancement on WS Code <-> Select By Source (Dashboard, DefectsPriority)
Enhancement3:
We might need to run a macro to support WS Code/Select By Source to be in sync at the end of Master data worksheet population and make sure the Dashboard first filter (Select By Source filter ) and DefectsPriority (Select By Source filter) need to be updated accordingly based on superadmin worksheet table Publish = "Yes" criteria.
Please let me know your thoughts on this.
Regards,
M
Mahendra S
Member
Thank you very much Luke, the functionality works perfect and awesome!!
Regarding the doubleclick proposal
I think above proposal works, but need some evaluation and let u know if any issues / symptoms.
Regards,
M
Regarding the doubleclick proposal
I think above proposal works, but need some evaluation and let u know if any issues / symptoms.
Regards,
M
Last edited by a moderator:
Mahendra S
Member
Hi Luke, The double click proposal is ok for time being till we have better solution. Wondering how googledocs implemented scroll bars in cells which is very nice feature where as excel do not have this capability unfortunately, any ways what ever we have now is fine.
I want to discuss a new enhancement applicable to both Master Data and DefectsPriority Tabs.
Enhancement: Is it possible to bring a dynamic column (Master Data and DefectsPriority tabs) between Status (ColumnH) and Priority (ColumnI) to represent some icons as shown in column F along with the criteria given in superadmin.
Basically after copyrows executed, based on status and priority need to populate icons in the new column(column width to be 4.5)
If above implementation is possible then one additional column will get added as columnI (header as 7star icon)
the Priority column will become columnJ and so remaining will move with one column.
Not sure how difficult to change, but if we implement the look and feel will change for template with great visual representation. I am sure this will become as state of the art implementation.
(attaching the template for above enhancement please see the super admin for icons)
Please let me know if you see any issues.
Regards,
M
I want to discuss a new enhancement applicable to both Master Data and DefectsPriority Tabs.
Enhancement: Is it possible to bring a dynamic column (Master Data and DefectsPriority tabs) between Status (ColumnH) and Priority (ColumnI) to represent some icons as shown in column F along with the criteria given in superadmin.
Basically after copyrows executed, based on status and priority need to populate icons in the new column(column width to be 4.5)
If above implementation is possible then one additional column will get added as columnI (header as 7star icon)
the Priority column will become columnJ and so remaining will move with one column.
Not sure how difficult to change, but if we implement the look and feel will change for template with great visual representation. I am sure this will become as state of the art implementation.
(attaching the template for above enhancement please see the super admin for icons)
Please let me know if you see any issues.
Regards,
M
This took a little while to setup, but I think it was a good idea, and it looks pretty nice. I got to learn some more about handling shapes, table column references, and shape placement. New macro in the TableEdits module, called "CopyShapes". I made it versatile enough that it copies the shapes to both MasterData and DefectsPriority. I even remembered to put the errCheck in this time.
Anyway, take a look, and put it through some tests to make sure no new bugs have appeared. If I don't hear from you, have a wonderful weekend.
Anyway, take a look, and put it through some tests to make sure no new bugs have appeared. If I don't hear from you, have a wonderful weekend.
Mahendra S
Member
Hi Luke, Sorry couldn't get back immediately this morning.
At first glance the template is looking pretty nice and very good visual representation.
I was just thinking this morning about the solution and glad we had something on icons.
One quick note the header I1 in 'MasterData' and 'need to show this icon
(basically the name of this icon is 7point star from Insert menu -> Shapes -> Starts and Banners 
I will do some more validation during weekend and If I see any issues will let you know Luke.
Have a good weekend!
Regards,
M
At first glance the template is looking pretty nice and very good visual representation.
I was just thinking this morning about the solution and glad we had something on icons.
One quick note the header I1 in 'MasterData' and 'need to show this icon
(basically the name of this icon is 7point star from Insert menu -> Shapes -> Starts and Banners I will do some more validation during weekend and If I see any issues will let you know Luke.
Have a good weekend!
Regards,
M
Mahendra S
Member
Hi Luke, Hope you had good weekend!
Few observations/issues I would like to share with you.
Please find attachment of template with latest Publishrun.
Also I have added a new icon for Postponed, in earlier version we missed.
Issue1: The icons that are getting displayed are in correct.
Please see below which is one example (Master Data H57 - H62) - closed icon is coming for RedyForDeployment
Issue2:Similarly there are rows which are mixed up with icons
pl look at row69 of Master Data the Postponed is mixed up with open (this may be b'cos of missing potponed icon but u can confirm)
Issue3: All the icons need to displayed at center of the cell in MasterData as well as in Defects Priority
Issue4: Please make MasterData and Defects Priority row height as const 15.75 for good visual representation of icons so that they can not be shown with shrinked as-is in MasterData.
Issue5: DefectsPriority tab pl see rows 10 to 14 the same icon is coming for ReadyToTest and ReadyForDeployment, this needs to be corrected.
Issue6:MasterData (I1) and DefectsPriority(H2) need to be shown with icon as given in G7 from superadmin/
Enhancement1: Need to check Postponed criteria as below
A defect with Status - PostPoned always need to be in Priority as Medium or Low.
If a defect in Postponed status and if no priority / blank then we need to make it by default 'Low' as priority.
If a defect in Postponed status and by chance priority is high or critical then need to covert in to Low as priority.
Please let me know the macro check of above Potponed status criteria.
Enhancement2: DefectsPriority tab we need to make sure only few columns are in write mode and rest of them in readonly.
Status, Priority, AssignedTeam, AssignedOwner, ETA To Fix, Progress/Resolution these columns need to be in editable and write mode.
and remaining colu,ms can not be editable and only readonly.
Please let me know on above enhancements and fix all the issues with icons.
Regards,
M
Few observations/issues I would like to share with you.
Please find attachment of template with latest Publishrun.
Also I have added a new icon for Postponed, in earlier version we missed.
Issue1: The icons that are getting displayed are in correct.
Please see below which is one example (Master Data H57 - H62) - closed icon is coming for RedyForDeployment
Issue2:Similarly there are rows which are mixed up with icons
pl look at row69 of Master Data the Postponed is mixed up with open (this may be b'cos of missing potponed icon but u can confirm)
Issue3: All the icons need to displayed at center of the cell in MasterData as well as in Defects Priority
Issue4: Please make MasterData and Defects Priority row height as const 15.75 for good visual representation of icons so that they can not be shown with shrinked as-is in MasterData.
Issue5: DefectsPriority tab pl see rows 10 to 14 the same icon is coming for ReadyToTest and ReadyForDeployment, this needs to be corrected.
Issue6:MasterData (I1) and DefectsPriority(H2) need to be shown with icon as given in G7 from superadmin/
Enhancement1: Need to check Postponed criteria as below
A defect with Status - PostPoned always need to be in Priority as Medium or Low.
If a defect in Postponed status and if no priority / blank then we need to make it by default 'Low' as priority.
If a defect in Postponed status and by chance priority is high or critical then need to covert in to Low as priority.
Please let me know the macro check of above Potponed status criteria.
Enhancement2: DefectsPriority tab we need to make sure only few columns are in write mode and rest of them in readonly.
Status, Priority, AssignedTeam, AssignedOwner, ETA To Fix, Progress/Resolution these columns need to be in editable and write mode.
and remaining colu,ms can not be editable and only readonly.
Please let me know on above enhancements and fix all the issues with icons.
Regards,
M
Mahendra S
Member
Sorry I mean to say we can fix all the issues with icons.
Thank you!
Thank you!
Re: Issue 1 & 5
Two things going wrong here, I've fixed one of them. Before, I did not provide a "do this if no match", so that last shape that was correctly chosen was getting copied. Now, I say correctly because there is a problem with Status. "Redy" is misspelled, should be "Ready". New setup shows these rows with no icons. IMO, this provides user with indication that there is a problem with row, and they should fix it in source data. This is also good because "Redy" would not be getting picked up in the Dashboard calculations.
Re: Issue 2
Correct, similar to above, this was because I did not have an "If Else", and no Postponed category. Issue has been resolved.
Re: Issue 3
Icons now get centered correctly.
Re: Issue 4
Row Height is now getting set. I'm afraid I don't know what this phrase means:
Re: Issue 6
Header Text for Icons has been hidden, Star Icon now gets copied over.
Re: Enchancement 1
After checking the Priority column, the DataCheck macro looks for Postponed cells and adjusts Priority, if needed.
Re: Enhancement 2
Protection status has been sheet, and sheet is now locked.
Two things going wrong here, I've fixed one of them. Before, I did not provide a "do this if no match", so that last shape that was correctly chosen was getting copied. Now, I say correctly because there is a problem with Status. "Redy" is misspelled, should be "Ready". New setup shows these rows with no icons. IMO, this provides user with indication that there is a problem with row, and they should fix it in source data. This is also good because "Redy" would not be getting picked up in the Dashboard calculations.
Re: Issue 2
Correct, similar to above, this was because I did not have an "If Else", and no Postponed category. Issue has been resolved.
Re: Issue 3
Icons now get centered correctly.
Re: Issue 4
Row Height is now getting set. I'm afraid I don't know what this phrase means:
Re: Issue 6
Header Text for Icons has been hidden, Star Icon now gets copied over.
Re: Enchancement 1
After checking the Priority column, the DataCheck macro looks for Postponed cells and adjusts Priority, if needed.
Re: Enhancement 2
Protection status has been sheet, and sheet is now locked.
Mahendra S
Member
Hi Luke,
Thanks for the update on all issues and enhancements.
I see a typo caused the mismatch with Redy but now changed to Ready and try to run.
However I see following err when I attempt to PublishSet
I think this is b'cos the DefectsPriority is getting locked and readonly.
Need to release the protection before write and protect the DefectsPriority at the end once it is complete.
I may be incorrect, but looks like that is the symtom.
Please let me know on this issue.
Also please see the icon is getting overlapped on the lines in Master Data (some times it is getting shrink, but not always), this is related to Issue4, hopefully if we are able to publish this will go off, but need to evaluate.
Regards,
M
Thanks for the update on all issues and enhancements.
I see a typo caused the mismatch with Redy but now changed to Ready and try to run.
However I see following err when I attempt to PublishSet
I think this is b'cos the DefectsPriority is getting locked and readonly.
Need to release the protection before write and protect the DefectsPriority at the end once it is complete.
I may be incorrect, but looks like that is the symtom.
Please let me know on this issue.
Also please see the icon is getting overlapped on the lines in Master Data (some times it is getting shrink, but not always), this is related to Issue4, hopefully if we are able to publish this will go off, but need to evaluate.
Regards,
M
You're correct, I forgot to unlock the sheet before FilterRows macro. Line has been added.
For the shapes, I'm not sure would cause that consistently. I was able to reproduce a few times, but couldn't find pattern. From research online, appears to possibly be connected to zoom level of worksheet, so I've set the code to zoom to 100% on those 2 worksheets before pasting in shapes.
For the shapes, I'm not sure would cause that consistently. I was able to reproduce a few times, but couldn't find pattern. From research online, appears to possibly be connected to zoom level of worksheet, so I've set the code to zoom to 100% on those 2 worksheets before pasting in shapes.
Mahendra S
Member
Thanks Luke. I will further look into but zoom issue looks like resolved with fresh Publish.