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
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
Sumit, you have to calculate the position of today within your timeline and use the relevant constants in the VBA code to get the position of your vertical line. Let's say your timeline goes from January to December and today is the last day of September, i.e. 75% of the entire project is over. The horizontal position of your vertical "today line" is then calculated as follows: cnstLeft + cnstStepsWidth + 75% * cnstTableWidth Finally add a vertical line at this position with the desired length. I hope this helps.
1 reply
Image
The slides and workbooks of my presentation on “Charts beyond Excel’s Chart Wizard” at SQL Saturday in London 2014 Continue reading
Posted Nov 24, 2014 at Clearly and Simply
Rob, if you get an error on the WebBrowser statement, I assume you are using Excel 2013, not Excel 2010, right? Microsoft disabled scriptable ActiveX components inside sheets since Excel 2013 and the approach of using a web browser on a worksheet is not working anymore. If you are indeed using Excel 2010, I have no clue why the code should not support the .Navigate method. The second issue you are reporting is indeed a problem. I noticed that, too. Have a look at the end of the previous post, where I am stating that this workbook is pushing Excel's computational capability to its limits. The out of memory issue may occur from time to time. I do not have a solution for this. I am sorry.
1 reply
Robert, thanks for your comment and your kind words. Unfortunately, no I didn't. I made some considerable enhancements for one of my clients (like more than one dimension in row and column headers, more filters, heat mapping the data by color, better sorting and many more). Since this was part of a paid project, I am not allowed to share it here. But I never looked into eliminating duplicate values. This wouldn't be much of a problem, I guess, just a lot of extra coding necessary. Anyway, the code isn't protected, so if you are familiar with VBA, you could give it a try on your own.
1 reply
Asit, to build your own Choropleth Map have a look at Tushar’s original article: Conditional colors of shapes and/or read the how-to blog posts here: Build your own Choropleth Maps with Excel Create Excel Choropleth Maps from Shape File If you want to display the values on the map, you have to add a textbox for each region on the map, position it manually in the center of the region on the map and link the textbox to the cell containing the corresponding value.
Toggle Commented Nov 16, 2014 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Beno, sure this is possible. I never tried, but I found this solution on VBA Express: VBA Express – Export from Excel to MS Project
Toggle Commented Nov 11, 2014 on Bring your tasks in a row at Clearly and Simply
1 reply
asa, have a look at Michael's answers to Andrew's and Kevin's comments above.
1 reply
Chris, 1. The month displayed in the header is assigned in this part of the code: With objMonthHeader With .TextFrame.TextRange .Text = intMonthNumber There is more than one way to skin the cat. For one, you could add a CASE statement here assigning the month name to .Text based on the month number. 2. The VBA code uses constants (cnstStepsWidth and cnstRowHeight) to define the width and height of the row header. It does not adjust automatically, but it wraps the text inside the textbox. You can change these constants to other values in order to make the header bigger, but you probably would also have to change other constants to make sure the Gantt Chart still fits on the slide.
1 reply
Jonathan, 1. Delete all shapes (counties and states shapes, i.e. the visible and the invisible) you do not need 2. Delete all rows on the worksheet of all counties / states you do not need 3. Run the Recreate Shape Index sub This should do the job.
1 reply
Charles, you could use LEFT and FIND to get the substring left to the searched word and embed this substring into the formula 38 – Extract Last Word.
Toggle Commented Oct 29, 2014 on String Calculations in Tableau at Clearly and Simply
1 reply
Eric, the changes work with Excel 2003, too. I will send you the XLS version by email in a minute. No donation area here. Thanks for offering, though.
1 reply
Eric, have a look here: Download Check List All per Column
1 reply
rperrett, the code in the model uses the property .Fill.ForeColor.RGB to color the shapes. If you want to define this value based on the RGB values, you can use this user defined function: Function udf_RGB (bR As Byte, bG As Byte, bB As Byte) As Long udf_RGB = RGB(bR, bG, bB) End Function You pass the R, G and B value of the color you want to the function and the function will return the color value, e.g. udf_RGB(100,100,100) will return 6,579,300 (a gray) udf_RGB(0,175,100) will return 6,598,400 (a green)
1 reply
Geethu, you also have to copy the code from the Microsoft Project Object "ThisProject". Those subs create the menus when opening the project file and delete them before closing. After copying the code to "ThisProject" you have to save and exit the file and reopen it again and you should see the menu.
1 reply