How To Use The ADDRESS Function In Excel

In this tutorial we’ll take a look at the ADDRESS function and how to use it to dynamically change the SUM range based on a selection from a Data Validation drop down list. We’ll also incorporate the INDIRECT, ROW, and MATCH functions.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

On a worksheet called “SalesData” I have a data range for the 12 months sales for 15 divisions:

112816-1

On a second worksheet called “Summary” I want to be able to pull in the sales from January to the selected month from the Data Validation drop down list:

112816-2

To accomplish this we are going to incorporate the following functions:

112816-3

And the ultimate formula we are going to create is:

=SUM(SalesData!B2:INDIRECT(ADDRESS(ROW(2:2),MATCH(Summary!$A$1,SalesData!$A$1:$N$1,0),2,1,”SalesData”)))

This is basically a SUM function formula with the first part of the range defined as the cell for January of that Division. The second part of the range, however, is using INDIRECT, ADDRESS, ROW, and MATCH functions to determine the dynamic cell location based on the choice made from the drop down list in cell A1 of the Summary worksheet.

In many past tutorials we’ve used SUM, INDIRECT, ROW, and MATCH functions, so we are not going to spend any time going over those. You can do a search for those functions on my website to find other explanations of those functions.

The key to this is the ADDRESS function.

The ADDRESS function is defined as follows:

“Creates a cell reference as text, given specified row and column numbers”

The syntax for the ADDRESS function is:

=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text])

Here is how we defined each of these arguments:

row_num – ROW(2:2). – here we used the ROW function to define what row we want the address to be in. As we copy the formula down, the row number will change to 3, 4, 5, etc.

col_num – MATCH(Summary!$A$1,SalesData!$A$1:$N$1,0). For the column number, we used a basic MATCH function to match the month selected in cell A1 of the Summary worksheet to the column headings on the SalesData worksheet.

[abs_num] – 2. This is an optional argument. The choices are 1 – Absolute; 2 – absolute row/relative column; 3 – relative row/absolute column; 4 – relative. Here you can choose what type of cell reference you want the result to be. We chose absolute row/relative column. The default is Absolute.

[a1] – 1. This is also an optional argument. You can choose the “a1” format (1) or the “R1C1” format (2). We chose the a1 style, which is the default.

[sheet_text] – “SalesData”. Here we need to indicate what sheet the cell reference is on. If it was the same sheet as the formula, we could have omitted it because it is optional. But since it is on a different worksheet, we noted the worksheet name is quotes.

So, in this case, since we chose November in our drop down list, the result of the ADDRESS function is:

ADDRESS({2},12,2,1,”SalesData”) – Row 2, column 12, absolute row/relative column, a1 style, on the SalesData worksheet.

And when we calculate the result of that we get:

=SUM(SalesData!B2:INDIRECT({“SalesData!L$2”}))

Now remember, the ADDRESS function “Creates a cell reference as text”. So we need to convert that text to a usable cell reference for our SUM function. That’s where the INDIRECT function comes in. The INDIRECT function “Returns the reference specified by a text string”. So it will convert that text “SalesData!L$2” into a usable cell reference for the SUM function, and we will get the result of sales for each division from January to the month selected by the drop down list.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , ,

No comments yet.

Leave a Reply