Oct 15, 2019  Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function. You don't necessarily have to type a function name in all caps, Microsoft Excel will automatically capitalize it once you finish typing the formula and press the Enter key to complete it.

Despite all the functions provided by Excel, you may need one that you just don’t see offered. Excel lets you create your own functions by using VBA programming code; your functions show up in the Insert Function dialog box.

Writing VBA code is not for everyone. But nonetheless, here is a short-and-sweet example. If you can conquer this, you may want to find out more about programming VBA. Who knows — maybe one day you’ll be churning out sophisticated functions of your own! Make sure you are working in a macro-enabled workbook (one of the Excel file types).

Follow along to create custom functions:

1. Press Alt + F11.

   This gets you to the Visual Basic Editor, where VBA is written.

   You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box.

2. Choose Insert→Module in the editor.

   You have an empty code module sitting in front of you. Now it’s time to create your very own function!

3. Type this programming code, shown in the following figure:

4. Save the function.

   Macros and VBA programming can be saved only in a macro-enabled workbook.

   After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook.

5. Return to Excel.

6. Click the Insert Function button on the Formulas tab to display the Insert Function dialog box.

   Finding the function in the User Defined category.

7. Click OK.

   The Function Arguments dialog box opens, ready to receive the arguments. Isn’t this incredible? It’s as though you are creating an extension to Excel, and in essence, you are.

This is a very basic example of what you can do by writing your own function. The possibilities are endless, but of course, you need to know how to program VBA.

Macro-enabled workbooks have the file extension .xlsm.

Summary

In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. You can use the INDIRECT worksheet function to create linked references to other workbooks. You can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with worksheet cell references as inputs.

More Information

Note

The INDIRECT function only returns the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

To create a reference to a workbook using three different cell inputs as references for the workbook, worksheet, and cell link, follow the steps in the following examples.

Example 1

1. Start Excel.

2. In Book1, Sheet1, cell A1 type This is a test.

3. In Microsoft Office Excel 2003 and in earlier versions of Excel, click New on the File menu, click Workbook, and then click OK.In Microsoft Office Excel 2007, click the Microsoft Office Button, Click New, and then click Create.In Microsoft Office Excel 2010, click the File Menu, click New, and then click Create.

4. In Book2, Sheet1, cell A1 type Book1.

5. In Book2, Sheet1, cell A2 type Sheet1.

6. In Book2, Sheet1, cell A3 type A1.

7. Save both workbooks.

8. In Excel 2003 and in earlier versions of Excel, type the following formula in Book2, Sheet1, cell B1:

   =INDIRECT('['&A1&'.xls]'&A2&'!'&A3)

   In Excel 2007, type the following formula:

   =INDIRECT('['&A1&'.xlsx]'&A2&'!'&A3)

   The formula returns 'This is a test.'

Example 2

In Excel 2003 and in earlier versions of Excel, you can replace the formula in Example 1 with multiple INDIRECT statements, as in the following formula:

=INDIRECT('['&INDIRECT('A1')&'.xls]'&INDIRECT('A2')&'!'&INDIRECT('A3'))

In Excel 2007 and Excel 2010, type the following formula:

=INDIRECT('['&INDIRECT('A1')&'.xlsx]'&INDIRECT('A2')&'!'&INDIRECT('A3'))

Note

The difference in how Excel references the cells. Example 1 references cells A1, A2, and A3 without using quotation marks, while Example 2 references the cells using quotation marks around the references.

The INDIRECT function references cells without using quotation marks. This function evaluates the result of the cell reference. For example, if cell A1 contains the text 'B1,' and B1 contains the word 'TEST,' the formula =INDIRECT(A1) returns the result 'TEST.'

However, referencing a cell with quotation marks returns the result of the cell contents. In the example in the previous sentence, the formula returns the text string 'B1' instead of the contents of cell B1.