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
Saket, I published this article back in 2012 and in the meantime, Google requires to wrap up the URL in an iframe and to pass a mandatory API-key. Have a look at the Google API documentation here: Google Maps Embed API
1 reply
Einar, yes this is possible. All you have to do is to copy the subs ExpandAll and Collapse All from workbook 13 into workbook 5, insert two command buttons and assign the two macros. As per your second request: yes this is possible and it shouldn't be too complicated. You would have to add a few lines of code creating a stamp for the topic in the subs ChangeTopicStatus and AutomaticSetTopicStatus. Unfortunately I do not have a ready to use workbook for you. As I mentioned in the article, there are countless possible combinations of the techniques and I do not have the time to create them all. Have a look at the VBA code, I am sure you will figure out how you can do it on your own.
1 reply
TK, I double checked and the links are still working for me, in IE and Firefox. Please try again.
1 reply
Saeed, I am not sure I understand your request correctly, but you may want to have a look at the compilation of check lists I recently published in this follow-up post: Microsoft Excel Check List Compilation Have a look at check list no 12. I think this could be what you are looking for.
1 reply
Image
A compilation of variations and enhancements of the interactive Microsoft Excel Check List Template Continue reading
Posted Jan 20, 2015 at Clearly and Simply
Gulgun, first of all I would recommend to use the faster version of the algorithm provided here: Faster Choropleth Maps with Microsoft Excel Same map, but it updates the colors 10 times faster than the algorithm provided in the article above. You can adjust the number of color bins and the thresholds on the worksheet [Control]. Delete the rows you do not need and adjust the thresholds as you like.
1 reply
Jeff, hard to say without seeing your workbook. Did you update the named ranges, too? Maybe it is a problem with the names. If you want, you can send me your workbook by email and I will have a look.
1 reply
Darius, unfortunately I do not have a 64Bit Excel version available, so I didn't know that. Many thanks for pointing this out and even providing a solution. This is greatly appreciated.
1 reply
Evan, I received your email and will send you an answer in a few minutes. Your problem is easy to solve.
1 reply
Evan, it actually always depends on the structure of the EMF file. You have to ungroup until there aren't any groups left. Very often, you have to delete some shapes which are not representing a region you want to color. Hard to say what the problem is with your map. You can send me your workbook by email and I will have a look.
1 reply
Shivaprasad, this is possible with some extra coding. First you have to check if the user clicked in the country column using e.g. ActiveCell.Column. If this is the case, you would create an SQL string like "SELECT […] FROM […] WHERE Country ="& ActiveCell.Value. You fire this SQL statement per VBA to the database and write the results to the second worksheet. Have a look at the last workbook of the article Motion Chart Excel Template where I used this technique to retrieve filtered data from an MS Access database.
1 reply
Andrey, in general it would be possible to write VBA code updating the Outlook appointments or tasks if something changes in Microsoft Project. However, this would need unique identifiers (e.g. which Outlook appointment corresponds with which Project task), a flag indicating which task has already been exported to Outlook and some complex error handling (e.g. what if the Outlook appointment was declined and isn't in the calendar anymore?). You can't do this with the tiny little code snippets provided above. Actually it would require quite some extra coding which has to be fired as soon as something changes in the Project file. Unfortunately I do not have the time to do this, but you have the code and maybe you want to give it a try? With the code provided above, you can only delete the appointment / task in Outlook manually and then export it again from Project to Outlook.
1 reply
Kali, there is a download link at the bottom right of the Tableau Public visualization.
Toggle Commented Dec 21, 2014 on The Power of Tableau Actions at Clearly and Simply
1 reply
Paul, if you have a data source containing all predecessors for each node (i.e. in your example 1 and 2 would be predecessors of node 3), a highlight action should do the job.
1 reply
Andy, I would assume, you have other objects in your worksheet than the shapes representing the regions of the map, like the drop down combo boxes in my example, right? If so, you have to make sure those are the last shapes on the worksheet. Go to the Selection Pane (ALT-F10) and make sure all shapes not belonging to the map (i.e. not supposed to be colored by the algorithm) are at the top of the Selection Pane, which shows the shapes in reverse order. You can use the arrows or drag and drop them in the Selection Pane. After you did, simply rerun the Recreate Shape Index sub (button on the control worksheet) and it should be working.
1 reply
Tim, actually it isn't only the completion rate which does not work if the items have a different number of checkboxes. Checking / unchecking all items of a topic, the change of the status of the topic after checking / unchecking an item and also the shading off of the items if all checkboxes are checked will not work. The code is assuming that each item has the same number of checkboxes and uses the fixed number of columns with checkboxes for various calculations. Changing the code and the conditional formatting is no rocket science, but unfortunately I do not have the time to do this at the moment. I am sorry.
1 reply
Michael, the gisinternal site seems to be unavailable sometimes or maybe they moved the download section somewhere else. Richard is kind enough to provide a copy of the ZIP-file in his dropbox: GDAL 1-9-0 Download it from there and try again.
1 reply
CC, this is possible, of course. It is pretty much duplicating and adjusting the existing code. Have a look here: Download Sort table by double click enhanced (26.1K)
1 reply
Kar, have you seen the section "Direct access to the source data via SQL" in the post above? Tableau's RAW SQL functions allow you to send SQL statements directly to the database.
Toggle Commented Dec 9, 2014 on Calculated Fields in Tableau at Clearly and Simply
1 reply
Lee, no sweat, have a look at this: Download Marimekko Template toggle value and percent (57.7K)
1 reply
Mike, I never had any feedback about issues with .xlsm workbooks. I just double checked (I simply saved the .xls as .xlsm) and the code works like a charm for me in Excel 2013.
1 reply
Kylie, this works for me: LEFT([Code], MIN(MIN(MIN(MIN(MIN(MIN(MIN(MIN(MIN( FIND([Code]+"0123456789","0"), FIND([Code]+"0123456789","1")), FIND([Code]+"0123456789","2")), FIND([Code]+"0123456789","3")), FIND([Code]+"0123456789","4")), FIND([Code]+"0123456789","5")), FIND([Code]+"0123456789","6")), FIND([Code]+"0123456789","7")), FIND([Code]+"0123456789","8")), FIND([Code]+"0123456789","9"))-1) However, as mentioned in the post, this is a very complex calculation and it may hit the performance of your workbook.
1 reply
Kylie, if you know for sure that the numbers are always at the end of the string (as they are in your examples), you could use the formula "26 – Find first number" to find the position where the numbers start and a LEFT function to return only the alphanumeric part left to this position. If you do not know for sure where the numbers are (e.g. BLUE123GRN is a possible value, too and the result should be BLUEGRN), I would recommend to make the changes directly in the database instead of using a Calculated Field in Tableau.
1 reply
Ewa, have a look at this: Download Choropleth Map Europe improved (275.7K)
Toggle Commented Dec 5, 2014 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Nelson, thanks for pointing this out. When I searched for a workaround, I found this article, too, but – as you said – it is a bit cumbersome, if you want to share your file with others (on a blog on with your colleagues) and you have to ask everyone to change the registry. It is just like you mentioned: some people do not have the necessary admin rights, others simply don't want to change the registry for good reason. Anyway, many thanks for the hint and the link.
1 reply