Excel

Excel Product function in Tableau

2020-02-20T16:29:24+00:00August 14th, 2018|Excel, Tableau|

I stumbled across a question on the Tableau forums asking how to write the Excel Product function in Tableau. Full credit for the answer goes to Brian Bell in the comments of this thread https://community.tableau.com/ideas/1857. The Excel Product formula in Tableau is: EXP (SUM( LN ([FieldName]) ) )   Should [FieldName] be a series of [...]

Excel unprotect workbook and worksheets using VBA

2020-03-05T10:53:20+00:00December 9th, 2013|Excel|

I recently inherited some Excel spreadsheets to modify which had protected workbooks and worksheets, but an unprotected VBA project. I didn't have the passwords for the Excel spreadsheets. If the workbook is created in 2007 or below the Excel passwords can be cracked quite easily. There are many links all over the internet which supply [...]

Excel Populate a Combo Box from a Database

2020-03-07T16:56:03+00:00April 18th, 2012|Excel, VBA|

To populate a combo box on a userform in Excel you need to use VBA. Firstly add a combo box to a userform in the VBA window (Alt-F11) of your spreadsheet. The VBA code below shows how to populate a 2 column combo box. Add this code to the Userform Initialize event to populate the [...]

Remove a line feed / carriage return from a string

2020-03-18T20:33:59+00:00August 23rd, 2011|Access, Excel, SQL|

This article describes how to remove the carriage returns and line feeds in SQL, Excel and Access. If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text. These unwanted characters can cause multiple problems, therefore removing or replacing them is [...]