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
Rocio, you have to expand the named range "myChecklist" to cover all topics and items of your checklist. Go to the Name Manager (FORMULAS tab), select myChecklist and expand the "Refers To" range. Furthermore, you have to take care of the "naming convention" in the first column. The items are "numbered" by the topic number followed by a period followed by the number of the item. See the section "How to use this template for your own checklists" of the article. As per your last sentence: I am sorry, I do not understand what you mean by "to add the user is not applying".
1 reply
Cristian, you are right, if you need a one-off solution to create freeform shapes from polygon data, QGIS is probably the easier way. What I needed was a VBA routine in a stand-alone Excel workbook doing this again and again on user input. That's why I created the workbook published above.
1 reply
Cristian, I am not claiming Excel would be the best tool to do it. However, I need the routine as a part of a another Excel project I am working on. Using another tool was not an option.
1 reply
Image
How to transfer polygon data into freeform shapes in Microsoft Excel Continue reading
Posted 4 days ago at Clearly and Simply
Tara, sure, with some extra VBA code you can export the checklist or parts of the checklist to Word, PowerPoint or another application. Here is a link to get you started: MSDN Export from Excel to Word The code snippet provided there exports an entire range to Word. You could build upon that and insert a loop through the items of the checklist and a condition to export only if the item is checked.
1 reply
Savy, consolidating data from several worksheets into a summary sheet by VBA is a very common Excel challenge and you can find hundreds of possible solutions available on the Internet. Here are 2 examples to get you started: MSDN - Consolidating Data from Multiple Worksheets into a Summary Worksheet VBA Express - Combine all Worksheets into one
1 reply
Fernando, as per your first request: as you have seen, I have posted an enhanced version of the Marimekko chart including the percentage options as a reply to your question in the comments section of the previous post. It is very easy to transfer the small code snippet to the Pivot Marimekko template. As per your second question: yes, possible and useful, but I am sorry, I do not have the time to oblige every request. The workbook is open and so is the VBA project. Please feel free to adjust and enhance the code as you like / need.
1 reply
Sherine, unfortunately no, I am not producing how-to videos about the material I am publishing here. I thought about it, but producing the workbooks and writing the articles already take a lot of time. Creating videos would take even longer. I recommend having a look at Chandoo's Excel School especially the Dashboard part. I haven't seen all of his material, but I am sure Chandoo is covering most of the techniques I am using here. If you have any questions about my workbooks, please leave me a comment and I will try to reply as soon as possible.
1 reply
Richard, in PowerPoint go to the INSERT tab, click on Object and in the upcoming dialogue window, you 1. select "create from file" 2. click on "Browse" and select the file 3. click on "Link" 4. finally click Ok. This creates an object (image) linked to the Excel workbook. The object will be updated every time you open the presentation or right click on it and select "Update Link".
1 reply
Fernando, have a look here: Download Marimekko Template toggle value percent total and per col (61.5K)
1 reply
Fernand, have a look here: Download Marimekko Template Toggle value percent both (59.7K)
1 reply
Richard, Have a look at the follow up article: The Implementation of Word Clouds with Excel This post provides a little Excel tool for free download to split continuous text into a table with single words and a standard Pivot Table to create the list with unique words and their count.
1 reply
Richard, actually there is a workaround to make scriptable AcitveX Controls work on Excel sheets: http://support.microsoft.com/kb/2793374 I never tried, because I do not want to tweak the registry. I am always passing my workbooks on to other people (on the blog or for my paid projects) and telling everyone they have to tweak the registry first is not an option for me. In the meantime I shared another solution without the ActiveX Webbrowser and Wordle, i.e. a stand-alone Excel workbook with a VBA routine creating the word cloud: Word Clouds with Excel The moment I am posting this I see from your latest comment that you already found it.
1 reply
Brandon, sounds as if you did not change the numbering in the first column. Please have a look at the section "Important" just above the download link. Each topic needs its own unique number, each item of this topic has the topic number followed by a period, followed by the item number. If you just copy one entire topic and do not change the numbering, the code will collapse all topics with this topic number. Just make sure to follow the convention and to have unique topic numbers.
1 reply
Savy, 1: you can make the textboxes resize to the length of the text by setting these two properties of the textbox: .TextFrame.WordWrap = False .TextFrame.AutoSize = ppAutoSizeShapeToFitText The latter is already set in the code, but the WordWrap line is missing. 2: to add the percentage complete inside of the bar, you can use the text of the bar shape (.TextFrame.TextRange.Text = […] and format and position it as you like. To color code it, you would add a Select Case statement (or an IF clause) and assign the relevant fill or font color based on the condition of the Case statement.
1 reply
Joost, 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.
1 reply
Casper, the cell coloring of the completion rate is done by simple Conditional Formatting with hard coded values of 75% and 100%. No VBA involved. Simply change the rules of the Conditional Formatting as you like.
1 reply
Savy, go to the VBE and search for the line .Text = DateFormat(varTask.Finish, pjDate_mm_dd_yy) It is line 484 in the code of the original project file posted above. Add the following at the end of this line: & " " & Format(varTask.PercentWorkComplete, "0%") & " " & Format(varTask.PercentComplete, "0%")
1 reply
Erna, I would strongly recommend against your idea, because it will clutter the display and add no value (the series names are already displayed in the row headers). However, if you really want to do this: go to the VBE and search for this line: .TextFrame.Characters.Text = Format(rngData(lngRowCount, lngColCount), CStr(rngData(lngRowCount, lngColCount).NumberFormat)) Add this at the end of the line: & " " & rngData(lngRowCount, 1).Offset(0, -1) But again, I wouldn't do that.
1 reply
Kirk, this formula removes the text including the signs ([Text] is the name of the dimension): LEFT([Text],FIND([Text],"<")-1)+ RIGHT([Text],LEN([Text])-FIND([Text],">")) This one keeps the less and greater sign: LEFT([Text],FIND([Text],"<"))+ RIGHT([Text],LEN([Text])-FIND([Text],">")+1)
1 reply
Gerard, unfortunately there is no easy way. It is possible, though (of course). The entire chart is created by VBA and the changes you want would require some adjustments in the code. The workbook is completely accessible, no password protection. The code is commented, so it should be easy to follow. Please feel free to change whatever you want to change.
1 reply
Image
Camera objects (aka linked pictures to cell ranges) can considerably increase the size of an Excel workbook. Why? Continue reading
Posted Mar 28, 2015 at Clearly and Simply
Image
Highlight one selected item across all charts and views of an Excel Dashboard: an example and the how-to Continue reading
Posted Mar 26, 2015 at Clearly and Simply
Image
How to create Word Clouds with Tableau and a look at the value of Word Clouds for serious Business Data Analysis Continue reading
Posted Mar 24, 2015 at Clearly and Simply
Ramesh, I assume you did not enable macros. The double click functionality and the completion rate calculation will only work if you enable macros. Check the Trust Center Settings (File, Options, Trust Center, Trust Center Settings, Macro Settings). I suspect it is set to "Disable all macros without notification" in your case. Change this to "Disable all macros with notification" and close the workbook. If you open it again, you should get a notification about the macros under the ribbon which allows you to enable macros for this workbook and the functionality should work.
1 reply