Blog

Today I’m writing about a dashboard project. The project involved some data importing and manipulation, but the ultimate output was the dashboard sheet with various reports generated on a weekly basis including custom filtering.  The best part of the project was the pictorial representation of the data which enables my client to immediately identify good and bad key performing indicators of the company.

image002

The user can run the report not only on a weekly basis but also by specifying a date.  The client used to do these reports manually each week, taking a full time employee a full day each week.  I improved the reports and eliminated the manual labor required from a day down to seconds.  The project cost the client about $1,000 but will save thousands in saved labor time and much more in cost savings and revenue enhancement through increased visibility of cost and profit drivers.

I’m including some code that supports the automation.

Set abc = ActiveSheet.DropDowns(“WeekDrop”)

With ThisWorkbook.Worksheets(“DashBoard”)

.Range(.Range(“DataField”), .Range(“rngData”)).EntireColumn.Hidden = False

lstvalue = CDate(.DropDowns(“WeekDrop”).List(.DropDowns(“WeekDrop”).ListIndex))

.Range(“rngData”).Value = .DropDowns(“WeekDrop”).ListIndex

Dim rngcell As Range

Dim rngUnion As Range

For Each rngcell In .Range(“G1″).CurrentRegion

If rngcell.Value > lstvalue Then

If rngUnion Is Nothing Then

Set rngUnion = rngcell

Else

Set rngUnion = Union(rngcell, rngUnion)

End If

End If

Next

If Not rngUnion Is Nothing Then

rngUnion.EntireColumn.Hidden = True

End If

.Outline.ShowLevels ColumnLevels:=1

End With

_________________________________________________________________________-_____

A while back, I did a project for a company called Kasian.  They recently were spotlighted for their effective solution.  See the link below.

http://www.ideateinc.com/ideas/case_studies/aec/success_kasian_ideate_bimlink.pdf

“Kasian contracted with consultant Rob Terry (ExcelAccessConsultant.com) to write an Excel segue. The segue would enable Kasian, with the help of Ideate BIMLink, to develop Room Data Sheets. The time saved by not manually inputting data is almost immeasurable.”

One of the interesting aspects of this project was the use of a vlookup within a vlookup.  A vlookup to determine the right column number in the reference table, and then a vlookup using that to pull the information.

Here’s a piece of code you can use from this project.  It’s to check cell values and dynamically insert a formula if the applied condition is met.

__________________________________________________________________________________

For i = 2 To Range(“Field_Column_Info”).Rows.Count

If InStr(1, Range(“Field_Column_Info”).Cells(i, 1).Value, “Header”) = 0 And Range(“Field_Column_Info”).Cells(i, 2).Value <> “” Then

Range(“Field_Column_Info”).Cells(i, 3).Formula = “=MATCH(” & HeaderColumn & i + Range(“Field_Column_Info_Heading”).Row – 1 & “,” & targetSht.Name & “!$1:$1,0)”

End If

Next i

__________________________________________________________________________________

01 Oct 2012 – Provo – Excelaccessconsultant.com announces their partnership with APFonline.org for conference and online training programs, on Advanced Excel Analytics for finance professionals.

This advanced excel spreadsheet analytics course will focus on using Visual Basic for Applications (VBA) script to write functions and custom macros. The use of User Forums which control macro events will also be discussed. All examples and illustrations will utilize financial treasury applications.

This Advanced Excel Analytics Pre-Conference Training is scheduled at Saturday, October 13, 2012. Registration is available for both conference attendees and non-conference attendees.

The Advanced Excel Analytics program will be conducted by Robert Terry, Principal, ExcelAccessConsultants.com, LLC.

Robert Terry one of the leading Excel Consultants, founded ExcelAccessConsultant.com in 2010 and has since delivered over 200 successful Excel and Access projects. Many of these projects have been for the finance, accounting, and treasury areas of the clients he serves.  Typical projects include expanding Excel financial models, automating financial reports using macros, and building Access databases to manage bond pricing.

Prior to starting ExcelAccessConsultant.com, Robert worked in the functional areas of financial and strategic analysis and data management for over 15 years at various corporations. Robert’s high level of proficiency in Excel modeling includes the areas of: profitability and cash flow forecasting, modeling to support strategic marketing efforts, modeling to support manufacturing functions of demand and supply planning, inventory analysis, project cost modeling, and sales team incentive modeling. Robert has also spent significant time working on IT-Finance cross projects such as data warehousing, report automation, systems conversions, implementations, and integrations.

Robert holds a BA in Economics from Brigham Young University and an MBA with an Accounting emphasis from Utah State University.

Based in Utah, Robert helps clients all over the country working remotely, using Gotomeeting, and sometimes traveling for extensive projects. You can contact him for Excel consulting, business data reporting and analysis solutions at rob@excelaccessconsultant.com or call 801-616-3702.

A lot of people tell me that VLOOKUP is unpredictable and restrictive. And while I disagree wholeheartedly that it is unpredictable, it is restrictive.

 VLOOKUP will not work unless the lookup_value is in the first column of the table_array. In real life we can’t always have our dataset that perfect. So in order to get rid of this limitation, we need to use the combination of INDEX and MATCH.

How? We’ll get into that, but before I talk about the use of this combination, let’s see some examples of these functions:

 

MATCH:

 This function looks for an item in a list and shows its position. For a more detailed explanation, see my tutorial.

Example:

Below Is my data:

I need to find the position number of “Flipout MOTOBLUR” from the table.

Let’s use the match function to find its position number:

Used syntax: “=MATCH(“Flipout MOTOBLUR”,B1:B26,0)”    (Including header in lookup array)

This will give us the result: 10     (Including Header)

 

INDEX:

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. For a more detailed explanation, see my tutorial.

 

Example:

We’ll use the same data used in the previous example.

We need to find the price of “Flipout MOTOBLUR” from the table.

Let’s use the Index function to find its price:

Used Syntax: “=INDEX(A1:C26,10,3)”

This will give us the result: 408 (Price of “Flipout MOTOBLUR”)

 

 

Combination of INDEX and MATCH

Now let’s use this combination to get the same result.

 

 

Now I have copied the syntax of MATCH which we used for finding position of “Flipout MOTOBLUR” and pasted that syntax within the used syntax of INDEX at the location we need to provide row_num. This gives us the same result as the result given by previous syntax.

Now you must be thinking that this would be easier with VLOOKUP. Yes, you are right. But suppose that instead of needing to find the price, you need to find the Brand of the given model. This is not possible with VLOOKUP, but it is possible with INDEX and MATCH.

This will give us the result:  “Motorola

The only difference is the column_num, which is now 1 instead of 3. We can also write a formula which will pick the column number automatically. If you want to see how, check the Example below:

 

If you have a specific project or need more help, you can contact me.

 

What is a query?

 
Say I have a library. To make it easier to keep track of my books, I put all of them into a document. Not only did I put the titles, but I put the author, date the book was published, blah blah blah. I really like my books.

Now I want to find all the books that were published before 1940. Using MS Access, I can create a query that would instantly bring all of them up for me. Cool, huh?

And there are all sorts of different queries you can use: “select”, “update”, “make table”, “append”… Pretty much, if there’s something you need from your database, there’s a query that will make it easy. Usually we create queries using the query builder tool available to us in MS Access and we save them as objects. These saved queries are seen as database objects with specified names. (example below)

     

All Access Objects – Queries – “update_completed”

 

How do I use a query?

 
So how can I tell the document that I want to see all the books published before 1940? We can access or run these queries using macros. (shown below)

  

Or we can use the docmd object and write a VBA code like:

 

For these methods we are just using queries that are already in the database.

 

How do I create a NEW query?

But what if I need information that I can’t get from using the queries that are already in the database? Sometimes the queries in the database do not fit our needs. We need something different. In these cases, we need to write an SQL Statement then use the command docmd.runsql “SQLCODE”.

Still with me? Ok, let’s take the query “update_completed” cited above. The SQL code for this query will look like this:

UPDATE tEmployees SET WHERE (((tEmployees.complete)=Yes));

The code to run this query will consist of the two lines below:

This is a very simple SQL Statement. Scary, right? Imagine the statements for more complicated queries. They are so difficult to decipher even experienced coders have a hard time following! If you’re feeling overwhelmed, that’s ok. Take a deep breath. There is an easy way of accomplishing this using the same query builder in Access Database that we use for designing regular queries. Just follow the steps below, and if you get lost or need more help, contact me. I’m always here for help.

 

Steps to turn SQL Statement into new Query
1. Design a query in the usual way but do not save it. This is how this query will look in the design mode:

  

2. Click the View icon on top left corner and select SQL View. The design view will be replaced by a text window. This is how the query will look now:

 

3. Copy this statement using ctrl+C and close the query window without saving the query.
4. Go to VBA code window and paste this code. Remove the line breaks and put the entire statement in between quotes as shown below:

 

This is an example of how to create a simple query using an SQL statement without saving it as a database object. You can create conditional queries using variables with this technique, too, but that’s for another post.

Note – you cannot use “select” queries using the docmd object – you can only use “Update”, “Delete”, “Make table” and “Append” queries.

I am currently working on an Excel tutorial section for my blog that will show you step-by-step how to perform different functions to make your work easier and faster. Working with Excel can be frustrating and time-consuming if you don’t have experience with the program. But with a few tips from an Excel Expert, you’ll probably wonder how you ever worked without it. Check back soon for easy-to-follow instructions that will quickly make you feel comfortable using Excel and show you how enjoyable work can be when you have developed some expertise.

So they tell me I need a blog. I’m official now.

A question came up on LinkedIn about which was the best BI reporting solution.

My comment:

If anything can beat Excel, I’d like to start providing it for my clients, but in my opinion Excel is still the best. The free programs like Pentaho, Microstrategy, and Jasper require too much training to be able to use. The user friendly programs like Qlickview or Tableau can be pricey. My clients usually have two different needs: nice looking dashboards that usually don’t come with interactivity requirement. And an OLAP tool for drill down analysis and building ad-hoc reports, which generally don’t have to be as professional looking. Excel is great for either option. An upgrade to BI tool like Tableau can combine both, but it’s usually overkill for most applications.

ogtzuq