The Back End of the Shaggy Dog

I work in tech, and people are forever referring to the "back end" of software, databases, and so on. I find the reference amusing (because inside, I'm 12), but I have to admit that the euphemism fits. The code, meta-data, and other unseen features are not the pretty face that gets shown around. So let's take a look behind the scenes, as it were, of my Shaggy Dog Story with Excel.These instructions are all about MacGuyvering a spreadsheet. While I hope that no one needs them "as is," there are a few tricks here that you might find handy for other projects.

First, I organized my resources on one spreadsheet. Then, I made it into a table. You can do this in Excel by clicking in the cell that will be the uppermost lefthand corner of the table, then using CTRL+T. My table had headers (unit, resource, title description), so I checked that box and hit ENTER. You don't have to turn the data into a table, but (1) I think it makes the formulas easier to manage and (2) you don't have issues with adding records.

Now I have something that looks like this. If you don't like the formatting Excel applies, you can go into the Table options and fuss around with it.

Then, set up your sheet where you want the results displayed. If you're using Excel offline, just use data validation. Don't bother with my "X marks the spot" strategy. However, if you're trying to webify this content, carry on. This is how I set up the demo for the blog post. I used the four column headers (in brown) on this sheet to organize the display.

There are two more small pieces before we get knee-deep with generating a list of items based on user input. First, on the sheet with the list of resources, I set up two IF statements, one for the choices underneath "Unit" and the other for "Category." I needed to tell Excel how to recognize the selections made by the user. Here is how I set up the formula for Unit:

This formula is placed on the other sheet, but since cell references are on this one, I'm showing it here.

Keep in mind that if you're building an offline version using dropdown menus, you can skip this part.

Finally, I set up a COUNTIFS statement based on the user selections. This was also placed on the page with the data set: COUNTIFS(List[Unit],G2,List[Category],G3). This tells Excel to count all of the cells in the table that matched the results of the two IF statements. I named this formula Item_Count. The one I did for work ended up with nearly 30 choices and two extra layers of IF statements...but we won't bother about that now.

An aside here...

I mentioned earlier in the post that converting the data into a table made things simpler for the formulas. Notice that in the COUNTIFS formula that I don't need to state a particular range (e.g. A1:A50). I can just tell Excel to use the table (List) and column header in square brackets (Unit, Resource).

Okay, let's go back to the sheet with the results. We have one more formula to apply...and it's a doozy. Why? We need to generate a list of results based on user input. Our old friends, INDEX and MATCH, are great at identifying the one item that fits a set of criteria...but we need a list. We need Excel to find the first one, the next one, the next one...and so on. Also, the results of each list might not be the same in number--could be one item, could be ten.  I found the basic answer here, if you want to watch a video tutorial; but here's the basic idea for cell B11 shown below. Keep in mind that this is an Array Formula, which means you need to use CTRL+SHFT+ENTER to make it work.

=IF(ROWS($B$11:B11)>Item_Count,"",INDEX(List[Title],SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))))

If you're hyperventilating after looking at that formula, find a paper bag and come back. We can do this.

The first part of the formula (=IF(ROWS($B$11:B11)>Item_Count,"") means that we want Excel to count the rows from B11 to B11 and compare them to the result of the COUNTIFS formula (named "Item_Count"). If there are more rows than the Item_Count, Excel should leave the cell blank. This part is important because we are going to have a variable number of results based on user input. By including the option for the blank result, we will keep error messages from showing up.

Next, we have the FALSE part of the IF statement---what Excel should do if the Item_Count is smaller than the number of rows. In this case, I want it to INDEX the Resources and display the title based on the following criteria:

• It must match both the Unit and Category as indicated by the results of the IF statements we set up earlier. (IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3)
• It must be the "smallest" (first) item that matches the criteria that is equal to the number of rows we've specified +1:  SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))

The good news is that you only have to build this formula once. And trust me, you will do your Happy Dance when it works. Use your fill tool to copy the formula down. You can also copy and paste the formula to cell E11 for the description, then change "List[Title]" to "List[Description]" in the formula.

You can download the workbook here, or go back to The Shaggy Dog Story and see how things work in the embedded version.

See? That isn't so Ruff! Ruff!

Bonus Round
The gigantic formula shown above has a lot of different uses. Don't discount it just because it looks scary. For example, think of how you might use it to sort a list of students into different classes (i.e. take a master list and sort it onto multiple spreadsheets). Although Excel is not a database tool, this formula gives you some options to use it that way when you need to.