This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Munich, Germany
Interests: new media, optimization, strategy, forecasting, business intelligence, project management, financial planning, business plans, telecommunications, revenue management, business performance management, simulation, pricing, intelligent data analysis, business cases, revenue assurance, cable operator, operations research
Recent Activity
Amish, I can’t reproduce your issue. If I have a task with a Finish Date 2/23/2017 and a % Complete of less than 100%, the task is marked as Late (as it should) if today is 2/24/2017 or later. If the task starts on Thu, 2/23/2017 and has a duration of 3 days, i.e. a Finish Date of Mo, 2/27/2017, today (Sa, 2/25/2017), the Status will depend on the % Complete: if the task has a % Complete of less than 67%, the task is Late, if the % Complete equals or is higher than 67%, the task is On Schedule, because there is still one day of three days left to complete it and only one third or less of the work has to be done. This works like it should from my point of view. What is not updated by Project is indeed the filter you may have applied. Let’s say you filtered all tasks with the Status Late and then change the % Complete of one task in a way which changes its Status from Late to let’s say Complete, this task will still be visible, i.e. the filter will not be updated automatically. You have to reapply the filter to get to the list of all late tasks.
Toggle Commented yesterday on Bring your tasks in a row at Clearly and Simply
1 reply
Arun, I just noticed that TypePad is not providing me with the email addresses of my commenters anymore. Can you send me a short email (click on the "Email Me" link at the top of the blog) and I will reply with the workbook from 2007 to 2017. Thanks.
1 reply
Arun, I will send you a version for 2007 to 2017 by email in a few minutes.
1 reply
Amish, Project stores the current status of a task as a number, not as the text that is displayed in Project: On Schedule is 1, Late is 2, Future Task is 3, etc. Have a look here: PjStatusType Enumeration The VBA code imports what is stored in Project and writes it to the sheet, i.e. the numbers, not the text. You either have to change the code to transfer the number to the according text or you create an extra column on the worksheet with a formula assigning the correct text to the number.
Toggle Commented 5 days ago on Bring your tasks in a row at Clearly and Simply
1 reply
Andrew, unfortunately no, at least not directly. The technique used above takes advantage of the chart method .GetChartElement to identify the data point the mouse cursor is currently hovered over. The Choropleth Maps consist of freeform shapes and there is no built-in VBA method to directly identify the shape under the mouse. I.e. you would have to develop your own VBA code to implement such a method. I never tried, but I assume this may be way more complicated than it sounds. Another option is displaying a customized tooltip after clicking on a shape of the map (e.g. using Application.Caller), but this wouldn't be a real tooltip anymore… Finally, you can show tooltips on a Choropleth Map using a hyperlink as shown in the article Fast Choropleth Map with Enhanced Features but there are no options to customize the tooltip.
1 reply
Arun, in the example workbook provided above, there is a named range called myCurrentValue (cell C10 on worksheet control). The VBA routines IncreaseCounter and DecreaseCounter increase or decrease this value with a delay in milliseconds passed to these procedures by the parameter lngWait.The VBA routines Forward, FastForward, Backward, FastBackward, ForwardToEnd and BackToStart then call one of these two routines to in- or decrease myCurrentValue as desired. These routines are assigned to to the play buttons on the dashboard and will be executed if the user clicks on one of the buttons. VBA increases or decreases the value of myCurrentValue and all calculations in the workbook are based on this value. I hope this sheds some more light on the technique.
1 reply
Anusha, the technique also works for tables with data entered via a data validation list and/or formulas in columns. Thus, your data set up cannot be the root of the problem. I can think of two possible issues: 1. The range name myData does not cover your entire range, i.e. you added rows at the end and/or columns to the right, but did not adjust the definition of myData. In this case, adjust the named range myData to cover the entire range where your data is. 2. You are not using a simple data range, but a table (list object). In this case, you would have to change the code to refer to a list object instead of a simple range. The code always runs to the end, because all errors are skipped due to the On Error Resume Next statement. If this does not solve the problem, you can send me your workbook by email and I will have a look.
1 reply
Nicki, my apologies for this late reply. What you have in mind is possible in VBA, of course. The current implementation simply differentiates between topics and items using two Boolean functions (IsItem and IsTopic). The functions check whether there is a separator (a period) in the first column of the list and if there is one, it is an item, otherwise it is a topic. For your purposes, you would have to change considerable parts of the code and not only check if there is a separator, but rather count the separators to decide which level has been checked. You can build upon the existing implementation and implement the necessary changes, but I assume this will require quite some extra coding and testing. Unfortunately, I do not have the time to do this for you. However, the code is open (no password protection), so maybe you can find a VBA expert in your office or a freelance Excel developer to help you with the implementation.
1 reply
David, the technique is possible on chart sheets, too. I am only working with embedded charts, so I do not have the code for a chart sheet available. However, I remember Ajay over at databison published a workbook with tooltips on a chart sheet a couple of years ago: http://www.databison.com/interactive-chart-in-vba-using-mouse-move-event/
1 reply
Vimal, can you post your workbook somewhere for download? It is easier to understand your issue / question with an example workbook.
Toggle Commented Feb 3, 2017 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
Christie, did you also add the name D_TUC to the data sheet and the assignment of D_TUC to S_TUC on the control sheet? If this doesn't fix the issue, you can send me your workbook by email and I will have a look.
Toggle Commented Feb 1, 2017 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Roy, I am not sure I understand your question correctly, but if you select "Show Header" for the hierarchy level dimensions on the Columns Shelf and hide the field labels for columns, the table should look as you probably want it to. If I misunderstood your question: can you post an example workbook somewhere? I will then have a look as soon as I have the chance to.
1 reply
Joe, this is the Excel workbook I used: Download 6_famous_paintings.xlsx (20052.7K)
1 reply
Joe, you can take any of them. Right click on the link and save the text file to your computer. Open Excel, import the downloaded text file (specify space as the separator), delete the first 6 rows, the last row and the first column and you have your data.
1 reply
Joe, yes, this is the site I downloaded the data from. See also the link provided in the section "The Data Source" of the article.
1 reply
Joe, here you go: 1. Organize the data, e.g. from the website mentioned in Data Source section of the article above: a simple table with two columns containing the X and Y positions of the dots of the painting and – as in my example – a third columns specifying the painting the data set belongs to 2. Open Tableau and connect to the data downloaded in step 1 3. Drag the "Painting" dimension to the filter shelf and select one painting. 4. Show the filter card and select "Single Value" and deactivate "Show All Values" in the Customize submenu 5. Drag X to the Columns Shelf and Y to the Rows Shelf 6. Right click on the X pill on the Columns Shelf and select "Dimension" 7. Right click on the Y pill on the Rows Shelf and select "Dimension" 8. Select Circle as the Mark type 9. Adjust color and size of the circles 10. Adjust the width of the view
1 reply
Richard, the error number indicates that your Excel options are set to disable all ActiveX controls without notification. Change this setting in the Trust Center to enable ActiveX controls and try again.
1 reply
Naveen, sorry for the late reply. Can you elaborate a little bit about why you are not able to sort the data based on the measure values? Or even better, can you post an example workbook somewhere so that I can have a look? Thanks.
Toggle Commented Jan 5, 2017 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
Dan, sure, send me your workbook by email (see email me link at the top of the blog) and I will see if I can help.
1 reply
sohaib, the workbook is available for download in the section "The Download Link" of the post. In case you already noticed this and had problems with the download: Microsoft Excel files are in fact zipped folders containing XML and other files. Depending on the settings of your system, it may well be that Windows tries to open the file as a zipped folder with Windows Explorer and then you only see the XML files. Simply right click on the link, select Save As and save the file to your computer. If you are using Microsofts's Internet Explorer, you also have to change the file extension from .zip back to .xlsm and you should then be able to open the file with Microsoft Excel by simply double clicking.
1 reply
Stefan, if you did not change anything in the code, it should work, even if products have different amounts of rows and columns. Check it out in the example workbook I posted for download. E.g. set the impact to "High" and the Probability to "High" for all risks of one Risk type on the Risk List sheet and select this Risk Type by using the filter drop down. The Pivot Table will then have only one row header and one column header. If this isn't the case in your workbook, I would assume something is wrong with the named range "myPivotTable". The code clears everything in this range first and then recreates the whole thing. I assume you do not have defined this name in your workbook and that's why the initial "cleaning part" of the code does not work and the old stuff remains on the sheet. If this is not the root cause of your issue, you can send me your workbook and I will have a look.
1 reply
Thomas, I gave that a try, too, but it didn't work for me. It takes only a few clicks to set up the query, but I never managed to get the entire job done. Loading the data took forever and always stopped with a "ran out of memory" error after a couple of thousand records. Anyway, thanks for the suggestion.
1 reply
Henk, sure: 1. in the worksheet object model: in the IF clause beneath the SELECT statement, delete "Or Target.Value = C_NA" from the IF condition and add it to the ELSEIF condition 2. in the sub ChangeTopicStatus of the module, add "Or varData(lngRowCount, lngActiveCol) = C_NA" to the condition of the third (the innermost) IF clause This should do the job.
1 reply
Mike, this is possible (of course) with a few extra lines of code. Have a look at this workbook: Download check_list_log_file.xlsm (35.9K) It is based on the original check list and writes all changes to a log sheet. To keep it simple, it is coming with one disadvantage, though: if the user double clicks on a topic, only the change of the topic status is logged, not the (automatic) changes of all its items. You could enhance the code to improve this (if you want), but I think this workbook should give you a jump start. Hope this helps.
1 reply
Enrico, you are absolutely right, this was actually a bug in the workbook I originally posted in my reply to Brian. It was simple to fix, though. I only forgot to adjust the second parameter passed to the UDF StatusCompletionRate. I fixed this now. Please download the workbook again and it should work. Thanks for pointing this out.
1 reply