Indirect References to a DSUM Parameter

Summary: Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be confusing. Here's an example of using an indirect reference within another formula. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Octavio has a worksheet that has a lot of named ranges in it. In one section of his worksheet he has a list of those names. In a formula that uses the DSUM function, Octavio wants to use different cells in this list to refer to the actual "database" that is used by the function. For instance, if "February09" is a named range and cell F12 contains the text "February09," Octavio wants to specify F12 as the first parameter in the DSUM function and have it get the actual range. When he tries the following, where Criteria is a named range for the summation criteria, he gets an error:

=DSUM(F12, "Profit", Criteria)

The solution to this is to use, instead of the actual cell, the results of the INDIRECT function. This function grabs whatever is at the cell it references, and then uses that content as a "pointer" to another cell or range. Thus, the following two formulas provide the exact same result:

=DSUM(INDIRECT(F12), "Profit", Criteria)
=DSUM(February09, "Profit", Criteria)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2885) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.

Check out Excel Graphics and Charts today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Indirect References to a DSUM Parameter