Excel yourself: Forms and functions

Excel Yourself

How to use formulas to pull information from a report into a coherent Excel sheet.

Question
I download a report into Excel, but its layout makes it impossible to use. Can you help?

Answer
In this case, while the report is badly laid out, it does have a structure that formulas can handle.

Figure 1

Figure 1

The layout of the report is shown in Figure 1. This will be in a sheet called Data.

The structure shown in Figure 1 is replicated all the way down the sheet for other employees.



There is a companion report that lists employee names as per Figure 2This sheet is named Staff. Note: the names start in row 11.

Figure 3 shows the layout we want to populate.

Figure 2

Figure 2

The monthly process will paste the new reports into the Data and Staff sheets. The layout in Figure 3 will then be automatically populated based on formulas. Note: Figure 3 columns match the sequence of rows in Figure 1. 

The formula for A2 in Figure 3 is: =INDEX(Staff!A:A,ROW()+9)

The INDEX function lets you define a range and then extract from that range based on a row number and a column number. In this case we don’t need the column number as we’re working with single column ranges. 

The range to extract from is column A in the Staff sheet. The row number is provided by the ROW() function. The ROW() function returns the row number of the cell it’s used in. In this case it returns 2. Nine is added to the result so that it refers to row 11 of the Staff sheet. This assumes the data always starts in row 11.

Figure 3

Figure 3

As the formula is copied down it will refer to each subsequent cell below row 11. It also automatically handles the situation where rows are deleted or inserted in the Staff sheet below row 11.

The formula for cell B2 in Figure 3 is: =INDEX(Data!$E:$E,MATCH($A2,Data!$B:$B,0)+COLUMN()-1)

This extracts entries from column E of the Data sheet (Figure 1) based on the relative position of the entries to the name in column B. Column C was added to the layout in Figure 3 to simplify the conversion. It will contain no data, but allows us to create a single formula in cell B2 that can be copied to the range C2:K2.

The MATCH function returns a number representing the position number of an entry within a range. In this case the MATCH function returns the row number of the employee name in Column B of the Data sheet (Figure 1). It will return 11 for John Smith. In column E, the Staff ID row is one row below the name row.

Figure 4

Figure 4

The COLUMN() function returns the column number of the cell it is used in. In column B it will return 2. By adding the column number to the MATCH result and then deducting one, we achieve an incrementing number that will refer to the rows below the name row as we copy the formula across columns.

The date of birth is in column G of the Data sheet, so the formula for cell L2 in Figure 3 is: =INDEX(Data!$G:$G,MATCH($A2,Data!$B:$B,0)+COLUMN()-1)

Figure 4 shows the results when the row 2 formulas are copied down.

The above formulas will return errors if dragged past the end of the data in the Data sheet. They can be amended to handle errors using the IFERROR function as below: =IFERROR(INDEX(Data!$E:$E,MATCH($A2,Data!$B:$B,0)+COLUMN()-1),"")

Download a sample spreadsheet.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]

This article is from the September 2014 issue of INTHEBLACK magazine.


July 2020
July 2020

Read the July 2020 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENT