Interactive Dashboard

We were recently engaged by a Fortune 500 company to create a set of charts (commonly known as a dashboard) to help them monitor their sales potential in the region. Before this, they were reviewing the sales forecast for each country separately and have to refer to many spreadsheets (all numbers) for the regional and global accounts. As too much time was spent calculating the sales for these accounts manually and therefore, they decided to approach us to help them solve the problem.

Presenting the numbers visually in charts is always a good option as charts are easier to read and understand than interpreting rows and rows of numbers. Drawing simple charts is a piece of cake for the client and they could have easily completed it all by themselves. The main problem in this project is however in the consolidation of the numbers in the spreadsheet for drawing the charts. If done manually, my clients would require a few days to manually update the charts. So we recommended using dynamic formulas like INDEX, OFFSET, MATCH and SUMPRODUCT for the calculations. Based on these formulas and other functions in Excel, a dynamic dashboard was created. Everything ran automatically like a program. All they had to do was updating the data worksheet and the Excel formulas would take over to update the charts. Other than automatically updating the charts,

  • Users have the flexibility to add new values in the setup worksheet which would appear in the dropdown list straight away;
  • When data from a new category is introduced into the data worksheets (e.g. new market segment called education) , the charts would intelligently to pick up the new categories automatically;
  • The charts always present five years of data. You can choose which year to start simply by selecting the year in the dropdown list and the chart will automatically present the five years of data starting with the selected year.

You can now download and experience the interactivity of this dashboard (sanitised version) by compeleting the form below:

 

A world of difference

During macro training today, I used a case example given by one of the trainees as an exercise for the class to practise their new found macro skills. In this exercise, they are required to make use of loops to run through all the 2000 records, picking up the last record for each product and putting them into another worksheet.

Continue reading "A world of difference"

Connecting to databases using VBA Macros

To connect to databases using macro, you will need to add Microsoft Data Access Objects 3.6 or Microsoft ActiveX objects 2.5 (minimum) to the reference (Tools -> References). Then write the following code to connect to the database and records.

For DAO:

Declare Variable - Dim dbs As Database

Connect to database - Set dbs = OpenDatabase(C:\foldername\databasename)

Access all records - dbs.Execute "Select * from tablename"

NB: replace the tablename with your Access database table name

For ADO:

Declare Variables - Dim cn As New ADODB.Connection 

Connect to database - cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\foldername\databasename.mdb;"

Declare Variables - Dim rst As New ADODB.Recordset

Access all records - rst.Open "Select * From tablename", cn

NB: cn is the variable name for the connection, rst is a variable name for recordset, replace the tablename with your Access database table name