Excel Training: Manual Methods

Option 2: Using Named Ranges, Intersection Operator, and Indirect

For standard workbooks, a simple copy-and-paste of title names and individual cell references can transform table data easy for a human to read into list data for computer analysis or for upload to a database.

But for our non-standard worksheets, we'll use named ranges to identify services and services packages that actually exist on the spreadsheet, the intersection operator to pull out data by service/service package combinations, and the indirect operator to help us quickly set up our formulas.

Use Named Ranges To Identify Services And Packages

If you're like most of our customers, you aren't quite confident that some lone wolf won't send in their own version of 'standard'. In this section, we put some little-known features of Excel to work in order to overcome this obstacle.

Named ranges are a great help in Excel spreadsheets. If you don’t know about them, see our June 2002 article on the web page. Sometimes a named range is a single cell like “AnnualGrowthRate” which is used in formulas all over the spreadsheet.

In our case, the named ranges represent the rows of services and the columns of service packages. We can then use the intersection operator to pull the values from each intersection of column named range and row named range. For instance, find the number at the intersection of Service4 and Bronze. This formula can be used to interrogate the spreadsheet and retrieve all of the data in format we need. Moreover, we can quickly create the named ranges on each spreadsheet as we open it. The problem of non-standard spreadsheets goes away! If one spreadsheet is missing a service or service package, it won’t mess up our formulas. Let’s try it.

Open a sample spreadsheet and select the range from A2 to the last used cell. Then go to Insert > Name > Create…

Intersection Extracts Data From Table Cells

The intersection operator works with a list of service, service package combinations. In the named range step, we created the ranges. In this step, we'll use the combinations to extract the relevant data.

Just like our previous example, this list can be kept in the master workbook and copied whole into each source workbook for ease of use. When ranges are missing (because the field rep didn’t put them in the report), an error will be generated but we can ignore them very easily in the final data sort.

If you haven't already seen the video for the standard version, you can watch it in Windows Media or Real Media.

The technique is the same except for the actual formulas to type in the third column. Watching the process may help make it more clear.

To create a list in the master workbook:

1. Copy the list of services.
2. Paste the list five times, one right after the other, for each of the five service packages.
3. Go back to the top of the workbook and copy the first package - “None”.
4. Highlight the cells alongside Service1 to Service11 and then click Paste – the word “None” will be copied alongside each service name.
5. Go back to the top of the workbook and copy each service package in succession, following steps 3 and 4.
6. Type the formula with the intersection operator into column three, and drag the formula to the bottom of the list, or double-click on the Fill Handle to do it automatically. We'll see how to implement this step in the paragraphs following.

If you’ve never used the intersection operator, you might want to try it once before using it in our list. As we mentioned, intersect goes to the overlap between named ranges and displays the result. Since we have row named ranges and column named ranges, our intersection is a single value.

The intersection operator is very strange in that there is no name, like =NPV() and no obvious operator like +, -, or *. The operator is just the space key, which we'll display as <SPACE>. To test this operator, go the sheet where you've created named ranges. Type the following into any cell:

 =(Service1<SPACE>None)
     Or really
=(Service1 None)

 You should get a value like 4 or 12 or 0: whatever is at the intersection of Service1 and None.

Have you got that? Good. We need to introduce one more little-known function in Excel called Indirect before we complete this part.

A Word From Our Sponsor

Indirect Function Allows For Changing References In Formulas

Imagine you have a formula that refers to cell like =A1*25%. But you have a problem. Sometimes the formula should be =B1*25%!

How can you create a formula that sometimes refers to one cell and sometimes another? The answer is the indirect function.

Indirect reports the contents of cell as though it were a reference to a range. Using the example above, place the text 'A1' in cell C1. Then create your formula like this: =Indirect(C1)*25%. Right now, the formula becomes =A1*25%, but if we change the text inside of C1 to ‘B1’, the our formula immediately changes to =B1*25%.

The indirect function is very powerful and can make your formulas very flexible. We’ll use indirect to refer to our named ranges Service1, Service2, Bronze, Gold, etc. If we've typed Service1 into cell A23 and None into cell B23, then the formula

=(INDIRECT(A23) <SPACE> INDIRECT(B23))

evaluates to the same thing as

=(Service1 <SPACE> None)

just like the equation above. The great thing is, we don't have to type in the formula into every line of our worksheet! We type the indirect formula once into the first row of the third column and use AutoFill to fill in the rest.

From here, our process is just like Option 1. Copy the formulas we've created on the master workbook formula sheet into a source workbook. Then copy the same region out again using Paste Special... Values into the master workbook consolidated data sheet. Each field rep worksheet takes seconds to process, and we don't care if the worksheets are standard or not!

The non-standard spreadsheets will generate a number of errors. We don’t really care because we expect the errors and we won’t copy those rows into the master workbook. Just as we did in Option 1, we sort the data by the hours column. The zero values will clump together, the error values will clump together and the real values will clump together. Copy all of the rows that are not errors and not zero and the use Paste Special…Values to consolidate them into the master workbook.

The analyst was distressed by the new Excel functions and the amount of time spent setting up these solutions. But when we were ready to go, the system clearly proved itself.

On spreadsheets containing a lot of entries, manually re-typing them took as long as ten minutes. Using this system, any worksheet takes only seconds.

These techniques are great, no doubt. They can be used in any number of situations. That's the great thing about training - you are constantly finding new ways to use your new knowledge. Save your applause, though, we're not done yet.

For pure brute force data handling, Excel VBA outperforms them all! Take a look through the next sections and see how custom application development using Excel VBA can be used in this process.