This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Greater Munich Metropolitan Area, Germany
I am helping people to turn data into smart decisions
Interests: Data Analysis, Data Visualization, Strategic Analysis, Financial Analysis, Strategic Planning, Financial Planning, Simulation Models, Operations Research, Optimization Algorithms
Recent Activity
Arturo, you are right, most of the links are not working anymore. Apparently, Richard deleted the files from his Dropbox and the Tableau Forum links are not valid anymore either. The post is more than 9 years old, and it was about a workaround for Tableau shapes. The workaround reached end of life. It is as simple as that. I am sorry.
1 reply
Latif, you do not have to change the criteria or the calculations in the model. You could use e.g. the pre-defined color scale "Red to Blue Contrast". If you don’t like the blue and want a "Red to Green" scale, adjust one of the pre-defined color scales on worksheet [control] as you like (starting with red and ending with green) and select this color scale on US Map worksheet.
1 reply
SK, template 08 posted above has more than one column with a checkbox (5 in total). If you need more than 5, simply insert an entire column somewhere in between the first and the fifth check box column, copy one of the existing columns and paste it into the newly inserted column. Finally adjust the second parameter of the completion rate formulas in row 3.
1 reply
Gareth, no, this is not possible with the current template posted for download above. If you really need the option to display 2 events in the same cell, you would have to adjust the existing formula in order to concatenate the text of a possible second item to the first one. This could get tricky, though, because the formula is pretty complex already. The second option would be to split every day into 2 columns and to display the text of the first item in the first column of the day and the (potential) second item in the second column of the day. This would probably be easier to implement, but it comes with a few disadvantages, too, e.g. less space to display the text descriptions. I am sorry, but I do not see an easy way to realize what you are looking for. That being said, in my humble opinion there shouldn’t be items (meetings and deadlines) on the same day and at the same time in a Project Calendar. If you are having a meeting, you shouldn’t have a deadline at the same time, because you can’t work on the delivery anymore. In other words, the deadline should be set to the hour right before the meeting starts.
1 reply
Gerardo, correct. Simply adjust the range on worksheet [Calculations] as needed and copy the formulas. Make sure the data sources of the chart cover the entire data range if you added more rows.
1 reply
Gerardo, just change the value in cell C13 on worksheet [Calculations].
1 reply
Matt, if you transfer the map, the sheets and the code to another workbook, there are quite a few things to consider, like making sure you copied the entire VBA code to your workbook or making sure the named ranges are available and correct in your workbook. My wild guess would be that you do not have defined the named ranges in your workbook (like myShapeNames, my Values, etc.). The code is referring to those named ranges and if you haven’t defined them properly in your workbook, the map will not update. So, please check the named ranges first. If this doesn’t solve the issue, you can send me your workbook by email (email link see the top of the site) and I will have a look.
1 reply
Hana, I just imported a Danish text with special characters like "æ" and "å". The split text tool worked like a charm for me. I do not know which issue you are referring to. Sorry.
1 reply
Albert, as far as I know, Excel does not provide an option to allow data entries on worksheets for selected users only. The only option I see is to write VBA code which unprotects the worksheet (either when the workbook is opened, or the sheet is activated) only if the username equals to one of the users allowed to see and edit the sheet. You would then write another VBA routine (e.g. Workbook_BeforeClose) to protect the sheet again.
1 reply
Mauk, thanks for letting me know. I fixed the link, please try again.
1 reply
Latif, I took the video of La Linea, separated it into 56 images using a video editing software, extracted the pixels from those images and calculated the X and Y coordinates of the relevant pixels (the line). But La Linea was just an example to show a somehow funny illustration of the approach. You can bring in your own data and the storyboard technique will still work.
Toggle Commented Jan 18, 2021 on Dynamic Storyboards in Excel at Clearly and Simply
1 reply
Latif, the data source of the line chart are the columns X Offset and Y Offset in the table on worksheet [data]. The values are calculated by formulas based on the preparing calculations on worksheet [calculations]. The formulas are not too complicated. Please download the workbook and have a look for yourself.
Toggle Commented Jan 18, 2021 on Dynamic Storyboards in Excel at Clearly and Simply
1 reply
Peter, I do not understand why you need sliders sitting on top of each other, but anyway: I just tried 2 stacked sliders based on the code in the workbook posted above and it is working fine for me. You have to make the other sliders invisible and keep only the slider on top of the stack visible (either manually or by VBA code), but I do not face any errors with stacked sliders. Since I cannot reproduce the error, it is hard for me to give any advice. If you want to, you can send me your workbook by email and I will have a look.
1 reply
Luca, sure, this is possible. If it is a one-time effort, you can transfer any data from Excel to Project by simply copying and pasting the data into the tables of Project. If you need to do this on a regular basis, you would have to write some VBA code in Project, which asks the user for an Excel workbook to be imported and then automatically imports the data. I do not have a ready-to-use code snippet for this, but it shouldn’t be too complicated to write.
1 reply
Tom, thanks for your comment. First, you are commenting on a 11-years old blog post here. In the meantime, I have done a lot of improvements and variations and published my results here. You should check out and browse through the category Choropleth Maps of this blog and you will find a lot of additional information how to create filled maps in Excel. As for your specific question: the code is only colouring shapes on a worksheet. The mapping of values to the shapes is done in a cell range named MapNameToShape. If you want to have a different Choropleth Map (like Canada in your case), you need to insert the map as freeform shapes, name the shapes and adjust the mapping of values to shapes. To get you started, have a look here: Build your own Choropleth Maps with Excel and/or here: Create Excel Choropleth Maps from Shape Files and, if you have Excel 365, especially here: Optimized Choropleth Maps in Microsoft Excel I hope this helps.
Toggle Commented Dec 1, 2020 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Ibrahim, here you go: map_yemen_by_governorates
1 reply
Dustin, the following blog post provides Choropleth Maps of Germany by ZIP-codes (PLZ2 and PLZ5) in Excel for free download: Create Excel Choropleth Maps from Shape Files If you need the greater Munich area only, you have to take out all shapes you do not need, adjust the input data range and recreate the shape index. This shouldn’t take too long. I hope this helps.
Toggle Commented Nov 20, 2020 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Andy, please have a look at my reply to Jason’s comment on December 18, 2017 above. I also posted an example workbook to download in my comment.
1 reply
Maria, I do not know of a way to display numbers as fractions in Tableau. The only way I see would be to create a Calculated Field dividing the decimal number into the integer, the nominator and the denominator and concatenating those parts separated by the fraction slash.
1 reply
Julie, the command button is the grey box in cells C1:E2 of the worksheet [Words]. The label of the command button says "Split text from a text file into words".
1 reply
Kallun, many thanks for your comment. I hear you. I think we are two of a kind here. Have a look at the workbooks at your own speed and let me know, if you have any questions.
1 reply
Image
The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020 Continue reading
Posted Sep 3, 2020 at Clearly and Simply
10
Dove, have a look at this workbook: Tooltips on xy scatter charts only first series.xlsm
1 reply
Dove, please have a look at my reply to Ben on March 23, 2017 regarding a chart with more than one data series. My reply also provides a workbook for download.
1 reply
anilayyar, the blurriness of the images comes from formatting them with the predefined picture style "Reflected Bevel, White". If you want to see clearer images on the carousel, you need to stay with the original linked images without any Picture Style Formatting. That being said, this solution is designed to be an interactive navigator. The user is not supposed to really view the charts on the navigator, but to select one and then easily jump to the worksheet with the selected view. Thus, the resolution of the images on the navigator sheet does not have to be perfect. The navigator only indicates what kind of visualizations are offered and provides an easy option to jump to what the user decides to have a closer look at. By the way: I edited your comment by taking out your email-address from the author's name in order to keep spam away from your inbox. I hope I acted in your best interest.
1 reply