≡ Menu

There is a division in the office between the have's and the have nots. Those that have taken the brave leap into the unknown and upgraded to Excel 2007 (or even Excel 2010) and those that are resolutely sticking to the old stalwart, Excel 2003. It's a nuisance. Perfectly decent spreadsheets are incapacitated when you [...]

Excel Pivot Tables

{ 3 comments }

How to Use Excel VBA to Log-In to a Website

Excel Macro web

How to Use Excel VBA to Log-In to a Website Thumbnail

I've been tasked with downloading regular data updates from a national reporting website but as this website has user log-in functionality. This means I have to set the macro up to open the webpage and then automatically enter the user ID and password. I found a very useful tutorial on accessing google webpages via VBA, [...]

Excel Macro web

{ 1 comment }
NHS Ref – Code to Grouper and PbR Tariff on your iPhone Thumbnail

The NHS Reference or NHSRef app gave me such joy but it is so niche that I almost missed it. The The NHS Reference iPhone app has been put together by the NHS Information Centre and is the perfect reference source for NHS professionals from clinicians, clinical coders, informatics analysts and finance staff. NHSRef functions [...]

Applications

{ 1 comment }

I use pivot tables a lot. They drive multiple reports in my workbooks and it is not unusual for me to have 6 or so pivots in each spreadsheet. When the underlying data changes I spend quite a lot of time clicking through each pivot table to refresh it. I was happy to discover the [...]

Macro Pivot Tables

{ 0 comments }

Macro to Insert a “Last Update On:” Date Value

Excel Macro

Macro to Insert a “Last Update On:” Date Value Thumbnail

I have a number of summary reports that are updated by the use of a macro update button. I usually include an area on the report to include a datestamp that indicates when the report was last updated or refreshed. This is the code I use: [cc lang="vb"]Dim VERSIONDATE As String VERSIONDATE = Format(Now, "dd-mm-yy [...]

Excel Macro

{ 0 comments }

Sumproduct Function and Conditional Criteria

Function

Sumproduct Function and Conditional Criteria Thumbnail

The sumproduct function is one of the most useful and powerful features of excel but the online help available from microsoft is unfortunately pretty poor. It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria. Here is the simple formula [...]

Function

{ 0 comments }
Macro to Consolidate Data Ranges from Multiple Excel Spreadsheets Thumbnail

I have a lot of macro workbooks that consolidate multiple data ranges from individual tabs in the same workbook into one summary tab but today I discovered how to consolidate data ranges from different excel workbooks into one summary workbook. My task today was to consolidate one of the worst NHS SLAM reports that we [...]

Excel Macro

{ 2 comments }

The SUMIF Function using Named Ranges

Excel Function

The SUMIF Function using Named Ranges Thumbnail

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name: areas of the spreadsheet, such as your data columns and rows or individual cells that may act as constants, for example the VAT value I’ve used the following named ranges in the SUMIF example below: As usual [...]

Excel Function

{ 1 comment }

Waterfall Charts that Cross the X-axis

Charts Excel

Waterfall Charts that Cross the X-axis Thumbnail

Waterfall charts have almost reached cult status in the office recently. They are popping up all over. We are well into the new years planning process and a waterfall chart is the perfect way to demonstrate how a starting position either increases or decreases through a series of changes. So in our case, when planning [...]

Charts Excel

{ 1 comment }

How to Test for an Integer Value in Excel

Excel Function

How to Test for an Integer Value in Excel Thumbnail

I was looking around today for an ISINTEGER logic function in Excel but I can save you the bother and tell you it doesn’t exist. I picked up fractional activity counts in our reported YTD position and wanted to quickly highlight which lines were affected so that I could assess the problem. You may expect [...]

Excel Function

{ 1 comment }
Page 3 of 3
1 2 3