Excel it a complicated beast. When I first started with spreadsheets I was always putting things in columns and rows and not doing much with it. Since learning VBA about 15 years ago I have taken my knowledge to the Nth degree. Recently, I ran a VBA workshop and one of the delegates asked me a question which perplexed me for a while.
“I want to be able to check two values and if they are equal then I want the “check” column to turn green and a plus sign be the value. Oppositely if the cell values don’t match then I want the “checked” cell value to be a minus sign and the cell colour to be red.”
Seems simple doesn’t it. Now try and do it without conditional formatting because that was my first thought too.
Continue reading Microsoft Excel – Using VBA to check values in different columns and apply formatting
I came across a question on LinkedIn from someone who wanted to know how to count the number of working days in a month. Now I know that there is a WORKDAY function within Excel which is supposed to take into account holidays but, this doesn’t take into account different national holidays in different time-zones (to the best of my knowledge).
Continue reading Microsoft Excel – Number of Working Days in a month
My last post I blogged about how to get the Day Names and Month Names using VLOOKUPs. However, today let’s look at doing exactly the same but, using VBA (Visual Basic for Applications). Now for the non-coders reading this it may seem kinda scary but, give it a go and you may be surprised about how easy coding in Excel can be.
I would recommend though that you learn in a practice document that isn’t going to affect real data or an existing document.
Continue reading Getting Day Names and Month Names in Microsoft Excel using VBA
When creating Dashboards, insight reports and spreadsheets it is something important to give day names or month names rather than a specific date string. Now this isn’t as hard as it looks but, all that Excel provides you with is a day number or month number. So why not translate that into something more meaningful?
This post will show you how to change day numbers and month numbers into names in two methods. The first by using VLOOKUPS (don’t worry they aren’t are terrifying as that) and secondly in VBA (visual basic for applications) which will be published in a second blog post.
Continue reading Getting Day Names and Month Names in Microsoft Excel using VLOOKUPS
Management Information. Business Analytics, Project Management, Training, Office365, Microsoft Office, VBA and Web Analytics