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
Jason, the code uses the defined start date of the project to draw the time line in PowerPoint. In Project, go to the Project tab on the ribbon, click on Project Information and change the Project Start Date to the date you want the time line to start with.
1 reply
SM, there is no such thing as a negative date, but you could use INT() or FLOAT() to convert the dates into numbers and the technique should work for dates as well.
1 reply
Marieke, as described in step 7 of the article, you should have another tab on the ribbon called CARTOGRAM DATA GENERATOR in Excel 2010 or 2013 for Windows. Click on this tab and then on the icon Create Cartogram Data at top left. I assume this may not be available in Excel for Mac. If you want to directly run the algorithm, you have to go to the code (module modMain) and change the line Sub CreateCartogramData(control As IRibbonControl) to Sub CreateCartogramData() You can then run the algorithm by returning to the worksheet, pressing ALT-F8 and clicking on the Run button.
1 reply
William, it does work, if you copy the entire sheet instead of inserting a new one and copying the cells to the new sheet. If you insert a new sheet, you also have to define the name myCheckList on this new sheet and copy the code from the original worksheet object to the worksheet object of the inserted sheet.
1 reply
Deniz, truth be told, the entire workaround of SVG- and EMF-files and ungrouping them in Excel does not work in all cases. It always depends on the SVG-file you are using and in the past I had a couple of SVG-files which didn't work with the technique described above. I can't tell you what the problem is in your case and I can't even guarantee it will work with your file at all. If you want to, you can send me your workbook by email and I will have a look and try to help. No promises, though.
1 reply
omkar, have a look at this Tableau Knowledge Base article for instance: http://kb.tableau.com/articles/knowledgebase/creating-filled-maps-pie-charts Instead of SUM(Sales) (as shown in the KB article), you would plot your locations on the secondary axis.
1 reply
jwfetz2, since version 2007, Microsoft Excel files are actually zipped folders including XML and other files. If your Internet browser opens Windows Explorer when clicking on the download link, right click on the link instead and select "Save Target As" to download. If you are using Microsoft's Internet Explorer, the IE will change the file extension from .xlsm to .zip during download. Simply change the file extension back to .xlsm and you can open the workbook with Excel by double clicking on the file.
Toggle Commented Aug 14, 2015 on Cartograms in Microsoft Excel at Clearly and Simply
1 reply
Thyssen, yes, this question has been asked before. Please have a look at Ricardo's and Pete Brown's comments above and my replies to their questions.
1 reply
hudonsedge, you didn't miss anything. I changed the code of the original template to meet your requirement.
1 reply
hudsonsedge, this workbook is based is based on the original template and it collapses only checked items: Check List - collapse only checked items I hope this will point you into the right direction.
1 reply
Jonathan, changes in the code aren't necessary. There seems to be an issue in the set-up of the workbook (names or named ranges). If you want to, you could send me your workbook by email and I will have a look.
1 reply
Jonathan, the technique does not work with all EMF files. If the ungrouping does not result in one shape per region, you have to find or create another map. You may either try to find another EMF file or a SVG file and transfer it into EMF using a vector graphic editor like Inkscape. Or you try to find an ESRI shape file, convert it into an SVG file using Indiemapper and convert the SVG to EMF using e.g. Inkscape. Finally you could try to find the polygon data of the regions and use the tool provided here Create Excel Freeform Shapes from Polygons to convert the polygons to freeform shapes.
1 reply
Jonathan, if you do not have 7 shapes after ungrouping, you can manually group the parts of the regions to 7 groups. If you then rename the grouped objects, the technique described above will still work. Regarding the renaming process: you select an object (freeform or group of freeform shapes) and either type the name into the name box (top left of the sheet, right above the header of column A and left to the formula bar) or go to the Selection Pane (ALT-F10), select the object there by clicking, click again and change the name.
1 reply
HK, unlike the standard shapes (rectangles, circles, etc.), you cannot assign text values to freeform shapes in Excel. So, you have to add one textbox for each country / state to the worksheet, add the name or abbreviation of the country / state to it and position it in the center of the states (on top of the freeform shape). You could do that by VBA, but since the state names aren't changing, it is a one-time effort and you could also do this manually. If you have only one map you are working with, doing it manually probably doesn't take much longer than writing the VBA code.
Toggle Commented Jul 29, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Willem, the code already categorizes the exported tasks. With the line .Categories = myTask.Project, the appointment is assigned to a category with the name of the project file. If you change this line to e.g. .Categories = "Mandatory Appointment", it would be assigned to the category "Mandatory Appointment". If this category already exists in Outlook, the according color will be displayed in your Outlook calendar. If not, there will be no color assigned. In this case the category will be shown in the All Categories dialogue as … (not in master category list). Of course you can also add categories to the existing master list and assign a color by VBA, but I have no code snippet available doing this. Shouldn't be too complicated, though. I am sure you can find the according code snippet in the web.
1 reply
Laurent, many thanks for your comment and detailed explanation. Interesting approach, although I do not really understand what you mean by "hover technique". Are you referring to Jordan Goldmeier's roll over technique (a UDF inside a hyperlink)? I am sure your approach works well and it is an interesting alternative. I am looking forward to see your workbook. However, the main idea of this article was a feature allowing the user to zoom exactly into an interactively defined area. Furthermore, with my approach, all other parts of the dashboard stay visible, i.e. you can still see and use the form controls, the legend and - if we have a dashboard with more than one chart – the entire dashboard. Having said that: I agree, it comes with a lot of disadvantages.
1 reply
Image
Interactive zooming in and out on Charts in Microsoft Excel Continue reading
Posted Jul 16, 2015 at Clearly and Simply
Asha, have a look at the workbook #2 in the follow-up post mentioned at the end of the post above. This workbook excludes the n/a checked items from the calculation of the completion rate. If you want to change this, you have to adjust the function CompletionRate. The function has 2 nested IF clauses within the For Next loop. Delete And rngCheckList(lngRowCount, lngColumns) <> C_NA from the condition of the first (outer) IF Add Or rngCheckList(lngRowCount, lngColumns) = C_NA to the condition of the second (inner) IF. This should do the trick. No offense, but I would also recommend having a look here: http://www.netlingo.com/word/shouting.php
1 reply
Christie, sure, send me your workbook with the data and a description of your requirements by email (see the link at the top of the blog) and I will see what I can do.
Toggle Commented Jul 13, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Mahendra, the macro UpdateFilterDropDown is assigned to the combo box (filter drop down). Every time you use the drop down, the macro is executed and inside this sub the main sub (CreateTextPivotTable) is called. If you have a second filter drop down in your workbook, you have to assign the macro to this one too.
1 reply
Connie, myStartPosition and myOverwritePosition are named ranges the code is referring to. If the code stops saying "Method Range of Object Global failed", you probably did not define these names in your workbook or the names in the workbook and the references in the code are different (a typo, maybe).
1 reply
Mahendra, unfortunately no. I do not have the time at the moment to implement change requests or enhancements. I am sorry.
1 reply
Fiosco, go to the VBE, select the form frmProgress, right click, select view code and in the code comment out or delete the line "On Error Resume Next". Run the macro again. If the code stops and you get an error message, click on Debug to get to the VBA and check at which line the code stopped (yellow background). Post another comment here or send me an email and describe what happened without the On Error statement.
Toggle Commented Jul 8, 2015 on Bring your tasks in a row at Clearly and Simply
1 reply
Mahendra, first you need 2 more parameter cells on the worksheet [control]: one to define the dimension used as the second filter and one for the target cell of the second filter drop down. You also have to define a name for the target cell of the second filter. Next, you have to adjust the VBA code. I can't explain every step in a comment. Search for "filter" in the code. Wherever you find it (variable, loop, comment, etc.), you basically have to duplicate the code. E.g. you need the variable varFilterElements twice, you need ddFilterDropDown twice, etc. In the section "Define Filter" you have to do all steps for both filters and so forth. Sounds complicated, but you have to explore and adjust only 2 subs (UpdateFilterDropDown and CreateTextPivotTable) with a total of only 220 lines of code.
1 reply
Image
Interactive radial or rectangular area selection on a map of the United States in Microsoft Excel Continue reading
Posted Jul 7, 2015 at Clearly and Simply