Excel Create Child Sheets From Master

• May 31, 2016
• Marcus Small

Recently my friend in Tasmania – Valerio – was kindly helping someone online (the kindness of strangers).  The problem was as follows.  The person had a master list and wanted to create child sheets from this master list.  Sort of a parent to child type exercise where a single list will produce multiple sheets (one to many - see image above).  The problem arises when you have some sheets which have already been created and some new sheets which need to be created.  As such we need to test for the existence of the sheet.  In the following article I explore the VBA code required to test if a sheet exists without the use of a traditional custom function.

Scandinavian Infograpic in Excel

• May 30, 2016
• Marcus Small

The above infographic is one of my favourites of the Nordic region. It is very simple and yet tells a story about 4 countries in Scandinavia. The Excel infographic has the top 5 companies for each country (Iceland is excluded due to its small size - sorry). It has data on career and staffing data. There is some population metrics, GDP and GDP Growth which is low all around the world as a result of the global downturn after 2008. Let's hope that ends soon.

Open Excel Update File

• May 23, 2016
• Marcus Small

At times you may wish to open a workbook, add a couple of items from the workbook you are working in to a list and close the workbook.  This type of update might be done on a specific set of cells and the results are added to the bottom of a list in a destination workbook.

Let's say we have data in Cells B10:B11 and we want to update our master workbook.

In cell B9 I have a path:

B9 = D:\Example1.xlsx

Excel Change Print Area with VBA

• May 12, 2016
• Marcus Small

Setting the print area on a moving range can be a difficult and frustrating thing to trap.  You may wish to trap a specific range with your print area or have the print range only consider certain columns.  Let's say you have a couple of helper columns in an Excel workbook and don't want these columns to be printed but you want the columns to be visible.  You could manually set the print area to exclude the print area but now you have more data which you have added and you would like the print area to update in kind.

Read Individual Columns to an Array

• April 12, 2016
• Marcus Small

In Excel VBA as I have shown previously you can push data between 2 or more arrays.  There have been a glut of examples on this website where I have moved data from one array to another.  There have even been examples where I have moved only certain columns into a single array.  The following method with you the INDEX formula within VBA to move data from one array to another but only move the columns which are specified.  With the following method of the 6 column array (Cols A – F) only columns A, C and E will be moved from the first variant, to the second variant.

Excel Import Data from Multiple Cells

• April 9, 2016
• Marcus Small

Recently a client asked me if I could create an Excel VBA procedure which picked up data from a file but the data came from multiple sheets and multiple cells in non sequential locations. Firstly, I thought the best method to do this would be to have a summary sheet which is hidden and simply pick this sheet up and consolidate it in the parent workbook.  However the problem was the files had already gone out and over 200 Excel files needed to be consolidated into a single workbook.

Excel What Column is Filtered

• April 8, 2016
• Marcus Small

Have you ever been using a large Excel file and wondered which cell am I actually filtering here?  Especially if you pick up someone else's file.  There might be more than one column filtered and there may be hundreds of columns.  You tend to go blind after a brief period of looking for that column with a blue arrow on the filter.  Well there is an alternative.  What if every time you filtered the worksheet a colour appeared in the cell(s) you were filtering.  You could see instantly which column was the source of the filter.

A Dynamic Pareto Chart in Excel

• April 4, 2016
• Marcus Small

A Pareto chart in Excel is a chart which is made up of a column chart and a line chart to show a cumulative position.  This is often a good chart to show an 80 20 scenario, where 80 % of the costs are achieved by 20% of the cost drivers.  The chart can be created by segmenting the the data into cost drivers or buckets.  If your business expenses were allocated into the following buckets:

• Staff

• Building

• Legal

• Accounting

• Rates

• Entertainment

• Marketing

Excel VBA Number of Printable Pages

• March 31, 2016
• Marcus Small

When I was younger the hashtag symbol was universally recognised as the symbol for a number.  Now it appears as the opening character in a tweet or other social media post.  I was recently asked to generate a procedure which would add the page numbers which were to be printed to the bottom of an Excel sheet.  The idea was when the file printed the first page had a description which said there will be X number of pages in the current report.  Excel does not currently have a generic report pages generator algorithm so here is a starting point.

Union An Excel Range and Transpose

• March 23, 2016
• Marcus Small

I was asked by a colleague to transpose a range in blocks of 5 from horizontal to vertical.  The data was arranged in 7 columns but he wanted the data split into from tabular data to headings on the vertical and the body text going across the columns.  Each block of data was colour coded and this colour scheme was meant to be kept in the output page.  It is an interesting problem and I decided to include a union range so I could include the header row (giving meaning to the data) each time a block of 5 rows was copied and transposed to the new sheet.

Excel Maximum Cell Length in a Range

• February 29, 2016
• Marcus Small

Finding the maximum length string in a range of cells can be handy especially if you are using the max length for another purpose.  The following custom function will find the maximum cell length in a range.

Function LongString(rng)

    LongString = Evaluate("Max(len(" & rng.Address & "))")

End Function

The custom function is called as follows:

= LongString(A1:A10)

Excel VBA Filtering on Time

• February 28, 2016
• Marcus Small

When filtering data the website has focused acutely on filtering information which meets a certain criteria or between two criteria.  The site has dealt with filtering on multiple criteria and filtering between dates.  Some of these concepts are quite difficult as repeating Excel's tick by filter is not always easy to replicate in VBA.  I came across an interesting problem while working for a client.  The range of cells had the date and time in the cell, the idea was to get quantity data from a dataset if it was past a certain time.

VBA Check if Sheet Exists

• February 21, 2016
• Marcus Small

I have a solution for checking if an Excel worksheet exists or not.  This can come in handy if you are creating a new sheet based on  a dataset.  You check for the new sheet and if the sheet exists you can alter the old sheet if the sheet does not exist then you can create a new sheet.

If Not Evaluate("ISREF('" & [A1] & "'!A1)") Then

Where Excel cell [A1] contains the name of the sheet you are checking for.

Now if this procedure was part of a loop you may wish to check all of the cells from say A1 to the last used row in column A.  Then the following might be more relevant.

Red, Green, Blue Interior Cell Colour

• January 24, 2016
• Marcus Small

Recently I was giving a half day course on heat maps and came up with the novel idea of creating a custom function which would identify the primary colour scheme for a cells interior colour.  It is in an effort to save a little time in the creation of a colour scheme for heat mapping.  Rather than laying down the colour and looking up the Red, Green and Blue numerical combination I simply lay the colour down and the custom function does the work for me.

Copy and Paste an Excel Chart into PowerPoint with VBA

• December 22, 2015
• Marcus Small

Creating Powerpoint presentations automatically is handy if you want the power to control another office application.  In this post I will copy a chart in Excel and paste it into a fresh instance of Power Point.  I will show two examples, once where the data will be pasted into a presentation with a title and one without.   This is a building blocks approach, creating a procedure which pastes one chart might seem like time better spent done manually but if there were 20 charts it would be faster for VBA to do the work for you. 

Add Picture to Excel Cell

• November 26, 2015
• Marcus Small

I noticed a post today on Ozgrid which asked if it were possible to create a situation where a picture could be added to a cell.  It got me thinking perhaps there is a way without the use of VBA.  So a bit of research and I happened upon this great technique, adding the picture to a comment.  This way your picture will appear as you hover over the cell.  There is no VBA required.  The following are the steps to take to achieve the task.

Insert comment in cell E3 right click the outer border and choose:

Import Website Data to Excel

• October 15, 2015
• Marcus Small

I was recently asked to repair a webquery which had gone bad.  The company controlling the website had changed the layout of the content.  I decided to extract the data from the website into excel using VBA and a call on the HTML tables inside of the website itself.  Obviously this technique works best where there are dedicated tables on a website.  It does not discriminate as all data from the page is imported but with some smart manipulation you can inject some VBA to extract the relevant tables into a fresh worksheet.

Convert Vertical Range to Horizontal with VBA

• September 12, 2015
• Marcus Small

I was recently asked by a my friend Joe to help him with an Excel problem.  He wanted to convert data in a vertical list into a horizontal list. This sort of thing is one of those things which is usually done with a pivot table.  What if your data is a unique ID and clients which can be attached to the same unique id.

Filter Source Data in Excel with Slicer

• September 10, 2015
• Marcus Small

I was recently answering a post on Ozgrid about filtering a list using a slicer.  The post was very similar to a blog post of mine with a slight twist.  The poster wanted the original list filtered based on the selection of the slicer and if no slicer item was selected then the filter was to be taken off the dataset.  I found the problem interesting because in order to solve the problem you had to know how many slicer items were in the list in the first place.

Excel VBA Colour Function

• September 8, 2015
• Marcus Small

This is an alternative to conditional formatting.  The following will detect the colours Red, Yellow and Green in a particular cell.  It will add the name of the colour – good for those colour blind individuals.  It is a custom function so needs to used in conjunction with a formula or in a VBA procedure.