How to Use VBA Code to Drag INDIRECT Formula Over Columns in Excel

The INDIRECT function in excel returns a reference to a range based on a text string. For example, =INDIRECT ("A1") returns the value in cell A1. The INDIRECT function can also be used to refer to other worksheets or workbooks by using the sheet name or the file name in the text string. For example, =INDIRECT ("'Sheet2'!B2") returns the value in cell B2 of Sheet2.

The INDIRECT function can be useful when you want to create dynamic references that change based on the value of another cell. For example, if you have a list of sheet names in column A, and you want to sum the values in column B of each sheet, you can use the INDIRECT function to create a reference to column B of each sheet based on the value in column A. For example, =SUM (INDIRECT ("'"&A1&"'!B:B")) will sum the values in column B of the sheet name in cell A1.

However, the INDIRECT function has a limitation when you want to drag the formula over columns or rows. The INDIRECT function will not adjust the reference inside the text string when you drag the formula. For example, if you drag the formula =INDIRECT ("A1") to the right, it will still return the value in cell A1, instead of changing to =INDIRECT ("B1"). This is because the text string “A1” is fixed and does not change when you drag the formula.

To overcome this limitation, you can use VBA code to create a dynamic text string that changes based on the column or row of the formula. VBA code can be used to manipulate the text string and insert the column letter or the row number based on the formula location. For example, you can use the Column property to get the column number of the formula, and use the Chr function to convert the column number to the column letter. Then you can concatenate the column letter with the row number to create a dynamic text string that changes when you drag the formula.

Procedures

To use VBA code to allow INDIRECT formula to drag over columns, you need to follow these steps:

1. Open the Visual Basic Editor (VBE) by pressing Alt+F11 on your keyboard.
2. Insert a new module by clicking Insert > Module on the menu bar.
3. In the module window, paste the following code:

Function IndirectCol (ref As String, col As Long) As Range
    'This function returns a reference to a range based on a text string and a column number
    'ref is the text string that contains the sheet name and the row number
    'col is the column number of the formula
    'For example, IndirectCol ("'Sheet2'!3", 2) returns a reference to cell B3 of Sheet2
    Dim colLetter As String
    colLetter = Chr (64 + col) 'Convert the column number to the column letter
    Set IndirectCol = Range (ref & colLetter) 'Concatenate the text string and the column letter
End Function

4. Save the module and close the VBE.
5. In your worksheet, enter the following formula in any cell:

=SUMIF (IndirectCol ("'Sheet2'!1", Column ()), "A", IndirectCol ("'Sheet2'!2", Column ()))

This formula will sum the values in row 2 of Sheet2, where the values in row 1 of Sheet2 match “A”. The IndirectCol function will create a dynamic reference to row 1 and row 2 of Sheet2, based on the column of the formula. For example, if the formula is in column C, the IndirectCol function will return a reference to C1 and C2 of Sheet2.

6. Drag the formula over columns to see how the reference changes. For example, if you drag the formula to column D, the IndirectCol function will return a reference to D1 and D2 of Sheet2.

Comprehensive explanation

The IndirectCol function is a custom function that we created using VBA code. It takes two arguments: ref and col. The ref argument is a text string that contains the sheet name and the row number of the range that we want to refer to. The col argument is a numeric value that represents the column number of the formula.

The function body consists of two lines of code. The first line declares a variable called colLetter and assigns it the value of the column letter that corresponds to the column number. The Chr function is used to convert the column number to the column letter. For example, if the column number is 2, the Chr function will return “B”. The 64 is added to the column number because the ASCII code of “A” is 65, and we want to start from “A” when converting the column number to the column letter.

The second line sets the return value of the function to a range object that is created by concatenating the text string ref and the column letter colLetter. The Range function is used to return a reference to a range based on a text string. For example, if the text string is “‘Sheet2’!3” and the column letter is “B”, the Range function will return a reference to cell B3 of Sheet2.

The IndirectCol function can be used in any formula that requires a dynamic reference to a range based on the column of the formula. The Column function can be used to get the column number of the formula, and pass it as the second argument to the IndirectCol function. For example, the formula =SUMIF (IndirectCol ("'Sheet2'!1", Column ()), "A", IndirectCol ("'Sheet2'!2", Column ())) will sum the values in row 2 of Sheet2, where the values in row 1 of Sheet2 match “A”. The IndirectCol function will create a dynamic reference to row 1 and row 2 of Sheet2, based on the column of the formula.

Scenario

To illustrate how the IndirectCol function works, let’s use a scenario where we have a workbook with two worksheets: Sheet1 and Sheet2. Sheet1 contains the following data:

Sheet2 contains the following data:

We want to calculate the total commission for each salesperson based on their sales and commission rate. We also want to drag the formula over columns to see the total commission for different months. For example, column B will show the total commission for January, column C will show the total commission for February, and so on.

To do this, we can use the following formula in cell B2 of Sheet1:

=B2*IndirectCol ("'Sheet2'!2", Column ())

This formula will multiply the sales value in cell B2 by the commission rate in row 2 of Sheet2, based on the column of the formula. The IndirectCol function will create a dynamic reference to row 2 of Sheet2, based on the column of the formula. For example, if the formula is in column B, the IndirectCol function will return a reference to B2 of Sheet2, which is 10%. If the formula is in column C, the IndirectCol function will return a reference to C2 of Sheet2, which is 15%, and so on.

We can drag the formula over columns to see the total commission for different months. For example, if we drag the formula to column C, we will get the following result:

=C2*IndirectCol ("'Sheet2'!2", Column ())

This formula will multiply the sales value in cell C2 by the commission rate in row 2 of Sheet2, based on the column of the formula. The IndirectCol function will create a dynamic reference to row 2 of Sheet2, based on the column of the formula. For example, if the formula is in column C, the IndirectCol function will return a reference to C2 of Sheet2, which is 15%.

We can also drag the formula over rows to see the total commission for different salespeople. For example, if we drag the formula to cell B3, we will get the following result:

=B3*IndirectCol ("'Sheet2'!3", Column ())

This formula will multiply the sales value in cell B3 by the commission rate in row 3 of Sheet2, based on the column of the formula. The IndirectCol function will create a dynamic reference to row 3 of Sheet2, based on the column of the formula. For example, if the formula is in column B, the IndirectCol function will return a reference to B3 of Sheet2, which is 15%.

Result

The result of the scenario is shown in the following table:

The table shows the total commission for each salesperson for each month, based on their sales and commission rate. The formula in cell B2 of Sheet1 is dragged over columns and rows to get the result.

Other approaches

There are other ways to achieve the same result without using VBA code or the INDIRECT function. Here are some of them:

• Use the VLOOKUP function to look up the commission rate from Sheet2 based on the name of the salesperson. For example, the formula in cell B2 of Sheet1 can be:

=B2*VLOOKUP (A2, Sheet2!A:B, 2, FALSE)

This formula will look up the value in cell A2 (Alice) from the range A:B of Sheet2, and return the value in the second column (10%). Then it will multiply the sales value in cell B2 by the commission rate. This formula can be dragged over columns and rows to get the same result as before.

• Use the INDEX and MATCH functions to look up the commission rate from Sheet2 based on the name of the salesperson and the month. For example, the formula in cell B2 of Sheet1 can be:

=B2*INDEX (Sheet2!B:G, MATCH (A2, Sheet2!A:A, 0), MATCH (B1, Sheet2!B1:G1, 0))

This formula will use the INDEX function to return a value from the range B:G of Sheet2, based on the row number and the column number. The row number is obtained by using the MATCH function to find the position of the value in cell A2 (Alice) in the range A:A of Sheet2. The column number is obtained by using the MATCH function to find the position of the value in cell B1 (Jan) in the range B1:G1 of Sheet2. Then it will multiply the sales value in cell B2 by the commission rate. This formula can be dragged over columns and rows to get the same result as before.

• Use the SUMPRODUCT function to calculate the total commission for each salesperson for each month in one formula. For example, the formula in cell B8 of Sheet1 can be:

=SUMPRODUCT ((Sheet1!A2:A5=Sheet2!A2:A5)*(Sheet1!B2:B5)*(Sheet2!B2:B5))

This formula will use the SUMPRODUCT function to multiply and sum the arrays that result from the following operations:

• (Sheet1!A2:A5=Sheet2!A2:A5) will compare the names in column A of Sheet1 and Sheet2, and return an array of TRUE or FALSE values, such as {TRUE; TRUE; TRUE; TRUE}.
• (Sheet1!B2:B5) will return the sales values in column B of Sheet1, such as {100; 200; 300; 400}.
• (Sheet2!B2:B5) will return the commission rates in column B of Sheet2, such as {0.1; 0.15; 0.2; 0.25}.

The SUMPRODUCT function will multiply the corresponding elements of the arrays, and convert the TRUE or FALSE values to 1 or 0. For example, the first element of the result array will be:

TRUE*100*0.1 = 1*100*0.1 = 10

The SUMPRODUCT function will then sum the elements of the result array, and return the total commission for January, which is 200. This formula can be dragged over columns to get the total commission for other months.