Compile Data and Produce a Dashboard

[Mahendra S]

Hi Luke, Thanks for your support.

Let me provide more details regarding the
enchancement3:
The Text that comes for all rows (starts at row4) with columns "Description" and "Progress/Resolution" columns, I am looking for vertical scrollbar inside the cell, but I believe vertical scrollbar in cell is not possible in my understanding.

However if above vertical scrollbar in a cell is not possible,
is it possible to have multiline text box for every dynamic row with the text that come from "MasterData" worksheet.



if above dynamic row population with multiline text box with text is not possible, please let me know.
This is good to have but optional.

Clarification:
The Master Filters (Env, Source & Assigned) values are not coming by default.
Above Filter values are getting populated only after "Transfer To Defects" button from " "DefectsPriority"

Please let me know is this a limitation.

Regards,
M

 

[Luke M]

Thanks for the clarification. You are correct, can't do vertical scroll bars in a cell. The closest I was able to do was to make a text box that appears/disappears. It uses a worksheet double-click event. Double-click a cell in Decription col and it appears, click somewhere else on the sheet and it disappears. Note that the box is not currently linked, so any edits in Text box don't affect your data. Check out the code at the bottom of the Defects worksheet module to see how it's setup, or if you want to tweak other things, such as box height, or to make it linked by removing the comment.

I added a workbook_open event so that the filters get populated right away.

 

[Mahendra S]

Hello Luke,

It was great to have filters working with default values,

Issue1: however noticed an issue with total getting displayed
If you have by default 19 rows it is showing 20 (All/All/All from master filters) also if you chnage any master filer one additional is showing in Total in cell-I1, I believe you are counting header row(2nd row) which shouldn't be,
is it possible to correct this issue, please let me know.

Also I want to build a summary sheet based on "Master Data" as attached in "NewDraft_Mar06- M"


I would like to attach the Group table from Super Admin worksheet to C12 cell in Dashboard worksheet.
The list values next to Select By Assigned need to populated in Listbox.

Then I need to bring all values by Matrix and fill appropriate
like Open Vs Critical in cell B14 and so on.
I believe this can be doable by COUNTIF logic (without lot of code) using Master Data worksheet, but need your expertise here.

and one more, every time now it is giving below warning when attempted to save file



I am not sure what is this about Document Inspector, please suggest what action required not see this warning.

appreciate all your help.

Regards,
M

 

[Mahendra S]

Also I would like to attach doubleclick text box to both description and progress/resolution cells, please adjust the code. This really cool feature. Thank you!

 

[Luke M]

1. You are correct, the summation formula was counting the header, and I forgot I had put an invisible formula in B1. Formula adjusted to give correct count.

2. Summary sheet table built. Uses a COUNTIFS formula, no need for any coding.

3. Document Inspector is my fault. I'm a bit paranoid about information, so I run a macro to strip my info after editing. Didn't realize it was affecting future saves. I've corrected my own macro, and fixed the workbook. FYI, to fix it, go under the Prep for sharing section (under File/Office button), and click this link:


4. Double-click range now includes Progress cells. Glad you like it!

 

[Mahendra S]

Hi Luke, Thanks for the update, really appreciate all the great work you have done!

Please let me know exact steps you did for attaching table gNames or GroupList (not sure which one) to the List in Dashboard worksheet (C12).

Here is another requirement I would like to discuss with you.

Requirement-03-08: Status/Priority/Assigned/ETAToFix Synchronization
Basically I would like to make synchronization between 'DefectsPriority' worksheet and other source worksheets for example 'Oracle Prod Defects' worksheet.

if I change a status of particular defect and /or Priority of particular defect and /or Assigned and /or ETAToFixof particular defectin DefectsPriority worksheet,
then above four columns data from row/rows that need to be updated in Source worksheets 'Oracle Prod Defects' for same row/rows with Status/Priority/Assigned/ETA ToFix columns associated cell values.
and this sysnchronization need to be dynamic and applicable across rows from DefectsPriority -> Oracle Prod Defects & DefectsPriority ->MS Prod Defects & so on..

Is it possible to have a Synchronization button operation from SuperAdmin worksheet so that to control the Status/Priority/Assigned/ETAToFix data across worksheets to reflect the same.
above columns data most of the time will get updated in DefectsPriority worksheet and that should reflect in all parent/source worksheets.

However Master Data need not be since we have CopyRows will always get latest data from other worksheets.

Please let me know your thoughts on above need

Also, how do I keep always the spreadsheet maximized window state, basically I want to remove resizing of the spreadsheet so the workbook stays always Maximized.

Thank you!

Regards,
M

 

[Mahendra S]

Hi Luke, Please let me know how complex is above synchronization automation, this is good to have which will make perfect sync between "DefectsPriority" and other source worksheets.

One way of doing by managing additional id between DefectsPriority and Source worksheets kind of Parent-child relation, but that might be quite complicated but not sure, need your expert advise and solution if possible.

Below are some smaller requests for your reference,

Please see the attached updated spreadsheet where you can see one additional column (Assigned Owner)across worksheets and looks like working after some adjustments, but please do a sanity check from code perspective.

Enhancement1: Need dashboard view for By Source, please let me know if you can attach code list (COL D)from SuperAdmin.

Enhancement2: Defect Open Date when it is brought to MasterData need to have a check whether the date is equal or lessthan currentdate or not, need to enforce and avoid to have future date.

Enhancement3: DefectAge is not getting calculated properly, basically want to control this from the code. but looks like the code is not controlling, would like to avoid manual formula, please see MasterData-P34 onwards going in to unknown date and this should always be number with zero digits only.

Enhancement4:Can we define and control the category and type format of dates always as 'mm/dd/yyyy' globally in MasterData worksheet, so that any date data copies into the MasterData always represent in same format. (may require a check to convert if date is in non "mm/dd/yyyy" format) and enforce category and type format in code itself(otherwise changing with Format cells by rightclick on cell is tedious and not user friendly).

Clarification: Is it possible to show the list box always with drop arrow in Dashboard
otherwise it doesnt show the side arrow by default

Please let me know possibility of enhancements.
Appreciate your great work to bring this to a great template.

Regards,
M.

 

[Luke M]

How to make names
First, we need to make a Named Range that refers to our Table. So, I went to Formulas - Name Manager, New Name, and gave the name "gNames" the formula:
=GroupList[Group]

Then, in the cell I want dropdown, I go to Data Validation, List, and type:
=gNames

Synchronization
This is going to be a LOT of work to do cleanly. As you alluded to, tracing back to original sheet is tough, since we don't transfer that info. Might be able to find the ID# if it was unique, but it's going to need to loop through the sheets for each record. IMO, it's better to keep your dashboards/reports an "output only" type style, and your raw data strictly inputs. So, yes, it can be done, but I would advise against it. Alternative idea would be that we can add a macro to highlight any cells that get changed. This would serve as indication that data is not "synched".

Enchancement 1 & Clarification
Drop down created, formulas created. Instead of using a cell's Data Validation, changed to ActiveX dropdowns so that arrow is always visible. As discussed before, feel free to edit the properties to change color, feel, etc.

Enchancement 2
What do you want to happen if greater than today? Set date equal to today?
New macro called "DateCheck" written to do this for now.

Enhancement 3
Macro does control the formula, see line 31 of macro. Row 34 was correct, was just formatted as date rather than number. Formatting adjusted.

Enchancement 4
The source of the problem is that your original data is being entered as text, rather than a number. This is why the formatting is not changing what you see. I added a line into the Formatting macro to reset the dates to be stored as numbers rather than text.

 

[Mahendra S]

Hi Luke, Tanks for your prompt replies always, great to see your some recommendations & improvements.

Issue1:Regarding, Synchronization, I understand the complexity and level of effort involved, I can live with out this.
However I like the "Alternative idea would be that we can add a macro to highlight any cells that get changed. This would serve as indication that data is not "synched".

Probably with different color code to identify so that I know which ones are changed in Defects Priority, and I can manually change in source sheets and then run "copyrows proc" to reflect in Master Data.
(Possible frequent changes will happen with status of particular defect and /or Priority of particular defect and /or Assigned and /or ETAToFixof particular defectin DefectsPriority worksheet)

Issue2: the Select By Source dropdown should only bring unique values, as-is it has duplicates.
can we avoid duplicates, as I understand source table has duplicates but can we get unique values.
Also the chart ttle is creating bit complex, would like to bring the title as "Not closed Defects for source"+<dropdown value>
Similarly for second chart title as "Not closed Defects for Group"+<dropdown value>

Issue3: Any reason why the columnf of DefectsPriority tab is showing with some blue color, probably this is coming from MasterData, can we just have table color in Master Data like u r showing alternative rows.

Issue4: DefectsPriority last row is not showing complete border, is it something in formatting, pl let me know.
Also is it possible to take out filters on row2 headers with in the code since we have dynamic filters in row1.
I know you can do manual filters but by default I do not want to show filters for all columns in row2 headers.

Issue5:Is it possible to have code to control the spreadsheet to stay always in maximized window state, basically I want to remove resizing of the spreadsheet(dragging/resizing should be disabled) so the workbook stays always in Maximized mode, and this should be applicable even when I close and open /double click the spreadsheet to open.

Please let me know possible solution for above issues.
(attaching the latest for your ref)

Regards,
M.

 

[Luke M]

Issue 1
Colored cell added to Super Admin sheet. This will control what color cells get marked.

Issue 2
The PopulateCBs macro now fills the dropdowns on Defects sheet as well as Dashboard sheet.
Titles have been changed as indicated.

Issue 3
Table formatting has been reset.

Issue 4
Border fixed with Issue 3 resolution. We have to leave filters on, as the dynamic dropdowns are in-fact adjusting the AutoFilter. I can remove all but the 3 specific columns, however.

Issue 5
The best we can do it place limitations within the XL application. We can't control what size the application is (it would be a security flaw if you could make something that can't be moved at all). For a simple start, I'm using FullScreen mode. The code is in the ThisWorkbook module. Do note that placing restictions on what a user can't do is very tough, as there are keyboard shortcuts, code writing, etc., that the user could do to override our controls.

 

[Mahendra S]

Hello Luke,

Very nice to see the charts, I was excited to see the titles.
But few observations and issues, I would like to bring it to your attention

Issue1: I want to make sure the the double click cell is attached to only Description and Progress/Resolution cells only.

Issue2: Is it possible to keep the history of the Progress/Resolution text for each cell belongs to the row, for example if I have 03/09, 03/10 text, when I enter new text as 03/11 it should keep old text as shown below and append at top the new text(This is most important) definitely need your thoughts and creativity to make this happen.

03/11 New code drop to be identified
-------
03/10: Dev started investigation and provided estimates.
-------
03/09: Failures in activex control related dependencies

Issue3: Is it possible to restrict the cell highlighting only for id, status, Priority related cells. Remaining cells I may not need to highlight.

Issue4: Dashboard Select By Assigned Team: list is not getting populated, something went wrong.
This is the same case with Master Filters in 'DefectsPriority' Tab (all 3 master filters select by env, source, assigned are not working, these are working before..)
Is it possible to control these master filters using tables from SuperAdmin worksheet rather writing code, please advise best optimistic way and proceed, at any point of time this should not fail which creates not a good user experience and feel.

Issue5:ETA missed color text is not showing in yellow.

Issue6:
Earlier you have asked,
What do you want to happen if greater than today? Set date equal to today? - Yes, please confirm this
New macro called "DateCheck" written to do this for now.

Requirement1: Need to lock the SuperAdmin worksheet and control with some username/pwd security, is it possible please advise with solution.
Requirement2: Is it possible to lock and unlock the ribbon menus using a button in superadmin worksheet.
I like the way you are showing the workbook when it loaded, but if you use 'restore down' it is loosing the default view and coming back with ribbons. (please see new file attached)


I appreciate all your great work on this template coming thru really great.
Thank you very much!

as always wait for your reply in the morning..
Have a nice day!

Regards,
M.

 

[Luke M]

Issue 1
Resolved, modifed the Worksheet_BeforeDoubleClick event

Issue 2
This is tricky. What if user appends data themselves? Or is everyone trained to over-write the data? If we write macro to do appending, and user appends, you'll end up with double data. Again, this is doable, but it gets quite tricky. We'd have to use the change event macro, and basically undo whatever was changed, save the cell's previous value, redo the change, compare the values, and then append.

Issue 3
Worksheet_Change event modified

Issue 4
I am unable to duplicate/detect an error. All dropdowns are showing correct information. Info gets loaded upon workbook opening, and when Defects sheet is refreshed. Note that if macros are disabled (or were disabled when workbook opens), things will not work properly.

Issue 5
Error on my part. When I re-sized the tables, this was over-writing formatting. FilterRows macro edited to change table size, and THEN apply formatting.

Issue 6
Confirmed

Requirement 1
Password and username have been setup. Check out the macro module "Security" for how to change them. Password is "password"
User name is "Mahendra"
button added to Super Admin sheet to hide it.
Again, note that security in XL is imperfect, and only slows people down. Someone with VB knowledge would still be able to open the sheet.
Requirement 2
I was able to get some help from this site:
http://www.mrexcel.com/forum/excel-...ide-ribbon-others-how-limit-one-workbook.html

Instead of using Full screen mode, I just disable the ribbon. Let me know if this works.


On a side note, if you aren't already, do try to read the code/formulas that I write to make sure you have an understanding of what they are doing. Inevitably, there will be changes that need to be made at some point and you will need to know how it all works. This will also help you develop and grow.

 

[Mahendra S]

Hi Luke, Thanks again for quick update!

Few things want to know,
For Requirement 1, is it possible to pop up the user name and password screen when I click on SuperAdmin sheet,
this should be applicable all the time, and once I have username/pwd credential it shoud take the values what ever currently you have in security. please let me know on this.

For Requirement 2
This idea (Instead of using Full screen mode, I just disable the ribbon) perfectly works!
however you are I need a way to enable the ribbons.
Can I have option buttons (enable and disable as shown below)


Would like to have enable / disable of ribbons based on option click.
Otherwise I dont have choise to open the ribbons from superadmin sheet, once they are disabled. Please let me know.

Issue 2
(This is tricky. What if user appends data themselves? Or is everyone trained to over-write the data? If we write macro to do appending, and user appends, you'll end up with double data. Again, this is doable, but it gets quite tricky. We'd have to use the change event macro, and basically undo whatever was changed, save the cell's previous value, redo the change, compare the values, and then append.)

I understand this is tricky, but only one person will do the changes and multiple people will not edit.
This is only for the Progress/Resolution column cells only.
Please let me know if this is still doable.

If you can let me know on Req1 & Req2 on priority that is helpful.

Issue2 looks like need more time I understand.

I am familiarizing myself to see the formula and code.

Thank you,
Regards,
M.

 

[Luke M]

For user name question, I'm not sure what you mean. The sheet is visible until you re-hide it, so shouldn't be bothered by excessing re-entering of credentials. Can you explain further?

Option buttons added, good idea.

If it's only 1 person, I might suggest just training them to click into the cell and make their change as opposed to trying to use a macro. But I think I've got it figured out.

 

[Mahendra S]

Hi Luke,

I only see Hide Admin button, once this hide button is clicked I see it is getting hidden.
But If I want to Un hide how are you controlling, is it thru a button or something else, Iam missing here the operation of Unhide.

because of above issue, I thought it would be nice to have login screen as soon as I click on super admin tab or on if I try to edit any row/col on super admin tab.

Please clarify above.
me the only person will edit the progress/resolution but the issue is when I try to copy rows/transfer rows I might loose the original text becuse it overwrites the data from other source sheets.(Master Data will be effected and brings latest data) at any time I will only have updates for high and critical progress.resolution rows.

Since we dont have the synchronization mechanism, the risk is the current text entered in DefectsPriority for Resolution/progress will be lost.

Please advise how to mitigate this content lose in progress/resolution.

Regards,
M

 

[Mahendra S]

Hi Luke,

Issue 4
(L: I am unable to duplicate/detect an error. All dropdowns are showing correct information. Info gets loaded upon workbook opening, and when Defects sheet is refreshed. Note that if macros are disabled (or were disabled when workbook opens), things will not work properly.)

The drop downs are showing up some times and if you change any of the filters they are not showing this is intermittent.

However based on filter selection, the number rows returned is always showing 22.
please see the screenshot below



I believe the rows getting returned are in correct for the combination selected.

looks like there is an issue, how ever is it possible to correct this issue please let me know.

But I am certain the filters were returning proper results few days back, not sure something else is creating this issue.
If you can not repro, probably I would like to go with out row1 filters since this is not working for.

Thank you!

Regards,
M

 

[Mahendra S]

Hi Luke, please see the attached spreadsheet.
I have included userform as sign on form. This is like modal form and this will allow based on user credentials.
once login and pwd matches then worksheet should be editable.

But need to write code for poping combo box with user name(ex: s.admin)and pwd as what ever you have mentioned, based on these, copyrows and other updates should be possible.

please advise with possible solution.

regards,
M

 

[ashish mehra]

Hey Mahendra & Luke,

I came across this post & found an excellent one for people who really wants to EXCEL in VBA.

I will give 5 stars to this one!!!!!!

Regards,
AM

 

[Luke M]

@ashish mehra
Glad this thread is helpful. It was certainly turned into an interesting project, and would make for a good study to see how the dashboard has developed.

@Mahendra S
I understand now what you mean about the user form. Rather than build a bunch a programming for a UserForm, I went with some basic input boxes, but still accomplish same goal. Security checks when you try to access Super Admin sheet. If incorrect, kicks you back out to Dashboard. If correct, gives access, and workbook will not ask again for credentials (until workbook is closed).

For the data preservation, I implement a basic check in. However, it only works when modifying a single cell. If you are going to be doing large copy/paste manuevers, I'm not sure how we can detect all the changes and preserve each cell. We could enable Tracked changes, but for some reason only Microsoft understands, you have to have a Shared workbook to do that. Is this limitation ok, or do you really need to do multiple simultaneous edits?

Regarding Issue 4, I found the problem. THe macro that hides the arrows was resetting the applied filter, so nothing ever got applied. I changed the order of things. Now, when you transfer to Defects sheet, the AutoFilter gets applied and only selected arrows are shown. Then the dropdowns can do their work w/o interference. This also enabled the count to be displayed correctly.

 

[Mahendra S]

Hi Luke,

Thanks for prompt reply for some reason I haven't received the auto email.
But when in logged in to forum I saw your reply.

I really appreciate your time and effort so far you have spend on this greatest template, for sure it will help to understand about the advanced features and sure helpful to others who wish to implement similar functionalities.

I still see the row1 master drop downs(Defects Priority) are not functional especially "Select By Assigned" only it is showing "ALL" this is the same problem we saw few days back and the same symptom with Dashboard "Selected By Assigned" it is only showing in the list "ALL". see below screenshots.




Clarification: I request and suggest can we use vlookup and table to retrieve the data and populate three different lists like in Dashboard worksheet.

I want to make it simple otherwise we ended up seeing the filter err's or non functional.
To achieve complete functional result, I believe we should go with excel list and rather combo boxes/activex controls.

Please advise.

Regards,
M

 

[Mahendra S]

Hi Luke, One more thing,

Issue1: looks like the multi line text box is not attached.

I would like to go with normal cell since the multi box is not functional, the text is not getting saved as you move to next row progress/resolution cell. This is more risk. Have you removed the related code for attaching the multiline box for Description and Progress/Resolution, please let me know.

Manual work around Operation:
what I can do is probably when I enter the text in progress/resolution column for each row with status high and critical, I will save separately in a worksheet and and replace the columns when I execute the transfer to defects.

(the pain point and caveat is if I dont have matching rows then the text I saved in another temp worksheet will get jumbled, i can live with this as long as I have less less than 20 rows.)


L: (For the data preservation, I implement a basic check in. However, it only works when modifying a single cell. If you are going to be doing large copy/paste manuevers, I'm not sure how we can detect all the changes and preserve each cell. We could enable Tracked changes, but for some reason only Microsoft understands, you have to have a Shared workbook to do that. Is this limitation ok, or do you really need to do multiple simultaneous edits?)

M: I will only edit one progress/resolution cell column for each row at any time, but will have updates for all rows

I still want to research a better way to automate this, otherwise this will be a big bottleneck !!!
can not afford to loose the progress/resolution history text for each row.

Please suggest any other workaround which is better than above manual operation.
Certainly do not want to go with extreme programming.

Regards,
M

 

[Mahendra S]

I have changed Uncomment the below line, but the text entered in multibox is not getting saved, any thing else need to do
.LinkedCell = Target.Address

please advise!

 

[Mahendra S]

The security instead of getting two seperate popups, is it possible to have single security screen, please advise.

 

[Mahendra S]

Hi Luke,

Issue1:The master filters issue in Defects Priority is intermittent. Not sure what is the issue some times works and some times do not.

But if u think if we use list+table combination instead of activex that might be good option. I am fine if the arrow dropdown is not visible, as we have this implemented first time in Dashboard filters.

Can we go with list +tables and show in dashboard tab(Select By Source & Select By Assigned Team filters) defects priority tab for all three master filters in row1 then we may not see these intermittent issues to have solid control on UI.

Issue2: cell content saving is still an issue if I edit in description and progress/resolution cells the text is getting lost when you move to next row or next column. If this fix requires lot of code tweak then I will go with normal cell instead of this double click text box.

Please advise on above issues.

Regards,
M

 

[Luke M]

I've tried one more shot at fixing the DropDown not populating. Please let me know if there is still an issue.

UserForm for password security has been utilized. Not quite as simple as input boxes, but should work.

On my machine, uncommenting the .LinkedCell bit is preserving the changes made in TextBox to the cell. Note that this is not a Worksheet_Change event, so the other macro which tries to preserve history will not get activated. Again, trying to preserve history like this, and/or having a scrolling linked TextBox are not how XL was designed to work, and it's going to make things complicated.

At this point, I would like to take a step back and review. While we have a very nice dashboard at this point, the problem that seems to keep causing issues is that our dashboard is grouping all the data, and we'd like to use that fact to make changes to the data. Workbooks work best when the data is all together. If I was able to, I'd start over with all the raw data on a single sheet. You can also add another column to keep track of the different sources. Once everything is on one sheet, it becomes much easier to filter the data where it is and make changes to it. We'd also be able to perform our data queries easier.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

Ok, enough of that, working with what we've got.

Possible ideas to make things easier:

• Get rid of ActiveX dropdowns, and just use a regular Table and AutoFilter dropdowns
• Removed Linked Text Box. This seems cumbersome, surely it's easier to just edit in cell directly?
• For "data preservation", we should have a time/date stamp on entries. If you want to make a "change", create a new line of data with new date stamp. You can then either show all the rows, or we'll rig something up to only show the latest entry.