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
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 5 days ago 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 7 days ago 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
James, this formula works for me, at least up to 999,999,999: IF INT(SUM([Data]) / 1000000) > 0 THEN STR(INT(SUM([Data]/1000000)))+","+ MID(STR(SUM([Data])),LEN(STR(INT(SUM([Data]/1000000)))),3)+","+ RIGHT(STR(SUM([Data])),3) ELSEIF INT(SUM([Data]) / 1000) > 0 THEN STR(INT(SUM([Data]/1000)))+","+RIGHT(STR(SUM([Data])),3) ELSE STR (SUM([Data])) END
1 reply
Maverick, have a look at the recent post: The Implementation of Word Clouds with Excel This article includes a small Excel tool for free download to split continuous texts into a list of words which can be used to create a word cloud in Tableau.
Toggle Commented Mar 20, 2015 on String Calculations in Tableau at Clearly and Simply
1 reply
Roger, sure, send me your workbook by email (email me link at the top of the blog) and I will have a look.
1 reply
Greg, I can't tell you what the specific problem was in Chris' workbook. As far as I remember he never sent the file, so I assume he figured it out on his own. If the code isn’t working correctly, there are a few things which may be wrong. Very often the names of the shapes and the defined names on the worksheet do not match (because of e.g. a typo, an additional trailing blank or a missing name, etc.). Another common root cause is an incorrect definition of the named ranges (e.g. it does not cover the full range). As I said above: hard to tell without seeing the workbook. If you want, you can send me your workbook per email or post it somewhere for download.
1 reply
Macan, the features are implemented in VBA (Visual Basic for Applications), Microsoft's programming language for Office applications. As is, they work in Excel only. It may be possible to rebuild the same functionality in Google Docs with Google's Apps Script, but I do not know. I am not familiar with Apps Script.
1 reply
Deepthi, sure, you will find an email me link at the top of the blog.
1 reply
Deepthi, with the existing code you can't have anything right to the checkbox column. The code expects to find the checkboxes in the last column of the list. If your problem is the fact that the checklist overwrites formulas, you can overcome this by replacing the following line Range("myCheckList") = varData by something like this: Dim i As Integer For i = 1 To UBound(varData, 2) Range("myCheckList").Cells(i, UBound(varData,2)) = varData(i, UBound(varData,2) Next i You have to do this in 2 subs (ChangeTopicStatus and AutomaticSetTopicStatus) This way, the code writes back only the last column of the list, i.e. the checkboxes. The columns left to the checkboxes stay unchanged, i.e. formulas or links inside those cells are retained. But again: the checkboxes have to be in the last column of the list.
1 reply
Deepthi, I am sorry, I do not understand what you mean by "set it for the specified range as such". The named range myCheckList covers the entire checklist and is used in the VBA code as a reference to the range on the worksheet in order to check if the current row is a topic or item, to check the current status and to set the new status depending on where the user double clicked. You can change the code to refer to specified rows and columns instead of the named range, but why would you want to do this? If you want to include extra code for sending email notifications, you can simply add this to the existing code. Let’s say you want to send an email notification to someone as soon as an item is checked: you would insert this new code right after the checklist code sets the status of this item to done.
1 reply
Deepthi, if you are inserting columns left to the last column (the checkboxes), it should work right away without any further changes. However, please be advised that the code always overwrites the entire content of the checklist with the values. If you have formulas inside the checklist, they will be overwritten every time the code runs. Having said that: it sounds as if you want to automatically send email reminders to email addresses specified in a column inside the checklist. This is possible, but it goes far beyond the very simple code for this checklist and would require quite some extra coding.
1 reply
Jeff, I am sorry, I can't answer your question. I forwarded your comment to Richard and he will look into this as soon as he has the chance to.
1 reply
Al, looks as if the code displays the number format ("General") instead of the headers and values. I can't reproduce this with the version I posted for download. Is this happening with the original file I posted for download or did you change anything in the code or workbook? If it is happening with the original file: which version of Excel and Windows are you using and which language pack? If you changed something: what did you change exactly or (maybe even better) can you send me your workbook by email?
1 reply
Image
Approach, algorithm, VBA code and performance optimization of the Word Cloud with Excel implementation Continue reading
Posted Mar 12, 2015 at Clearly and Simply
Yvonne, I am sorry, I don't get it. What do you mean by "the above checklist pops up"? What exactly did you copy from the workbook posted above? The entire code or just the command button? Can you send me your workbook by email?
1 reply
Denise, you can do this with simple Conditional Formatting: select "Format only cells that contain" and "Cell value equal to ž" and set the font or fill color as you like. No VBA required.
1 reply
Pooja, Excel files are zipped folders containing XML and other files. Unfortunately Internet Explorer renames .xlsm files to .zip files during download. Change the file extension from .zip back to .xlsm and you can open the workbook with Excel.
1 reply
Jen, you have several options to filter a list: 1. The easiest way would be to use Excel's filter function (HOME tab, Sort&Filter) and filter the list to only show the items with TRUE. 2. You use an array formula which filters data from one list in another list based on a criteria. Let's say you have the items of your checklist in range A1:A20 and the TRUE/FALSE values in B1:B2. Array enter (CTRL-SHIFT-ENTER) the following formula in C1 and copy it down to C20: =IFERROR(INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20,ROW($A$1:$A$20)-ROW($A$1)+1),ROWS($A$1:$A1))),"") You will then get in column C a list of those items only which have TRUE in column B. 3. Third option would be a small VBA sub to filter the list and write it back to the worksheet.
1 reply
Jen, you are absolutely right, this is an error in my article. It has to be "yyyyMMdd" instead of "YYYYMMDD". I am sorry. Thanks for pointing this out. I updated the post now. I agree, the syntax of the format parameter in DATEPARSE is confusing. However, this isn't Tableau's fault. The syntax for these symbols was defined by the International Components for Unicode (ICU) formatting language. Have a look here: ICU User Guide - Formatting Dates and Times
Toggle Commented Mar 10, 2015 on String Calculations in Tableau at Clearly and Simply
1 reply
Sergio, as mentioned above, Microsoft Excel files are in fact zipped folders containing XML and other files. Unfortunately, the Internet Explorer changes the file extension from .xlsm to .zip during the download. Simply download the file as posted in the article and change the file extension back to .xlsm in your Windows explorer and you can open the file with Excel.
1 reply
Anna, it is still working for me (Excel 2013) as described in the article. If the Slider Control isn't available in the "More Controls" list on your machine, I would assume the ActiveX controls are not installed. Please also see the chat I had with Lieven above.
1 reply