ADDRESS Formula Excel – How to use Excel ADDRESS Function




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Syntax of ADDRESS Formula
Example of ADDRESS Formula
Possible Errors returned by the ADDRESS Formula

ADDRESS formula in Excel generates the address for a cell when provided a row and a column number.

ADDRESS Formula Syntax

ADDRESS Formula has five parts:

ADDRESS (row_num, col_num, ref_type, ref_style, sheet_name)

address-formula-example-and-syntax

row_num

row_num is the row number in which the cell for which you are trying to generate the address is located. It is specified as a number.

col_num

col_num is the column number in which the cell for which you are trying to generate the address is located. It again is specified a number.

ref_type

ref_type type of address you want to return. Bear in mind that there are three address types, namely :
Absolute (Ex. $A$1)
Relative (Ex. A1)
Mixed – Absolute Row and relative column (Ex. A$1) or Absolute column and relative row (Ex. $A1)

To get the absolute reference type of cell address using the ADDRESS formula, you can either specify this parameter as 1 or leave it blank (Ex. =ADDRESS(1,3,1) or simply =ADDRESS(1,3) ) The optional parameter is optional in the ADDRESS formula and when left blank the formula will generate the absolute type of reference.

ref_style

ref_style is the reference style which signifies whether the address returned in formatted in the A1 style or the R1C1 style. This needs to be either TRUE or FALSE (FALSE can also be specified as 0 and TRUE by any number <> 0).

A1 Style Reference – Will show the cell address as [col][row] (Ex. A2, or $A$2)
R1C1 Style Reference – Will show the cell address as R[row_num]C[col_num] (Ex. R[1]C[2] where the numbers in the parenthesis indicate the row and the column numbers respectively.)

This parameter is optional and if omitted will return the A1 type of reference. You can specify this as =ADDRESS(1,2,4,TRUE) or =ADDRESS(1,2,TRUE) (omitting the third parameter) or =ADDRESS(1,2) (omitting the third and the fourth parameter).

sheet_name

sheet_name is the sheet name in which the cell is located. If nothing is specified, the sheet name is omitted from the address being returned by the formula. If the cell is located in any other worksheet than the present one, you can specify this as a string enclosed in quotes (=ADDRESS(1,2,4,TRUE,”Sheet2″) ). For this parameter to work correctly, you need to have both the ref_type and ref_style parameters specified correctly.

Example of a ADDRESS Formula

how-to-enter-the-address-formula-in-excel
(Please note that the last three parameters are optional and are usually omitted)

Let’s look at an example of the ADDRESS formula. Suppose we wanted to generate the address for a particular cell in which a value is stored. We could simply write =ADDRESS(1,2,4,TRUE,”Sheet1″). The result in this case will be Sheet1!B1 as shown in the example above. We could also have entered the formula as =ADDRESS(1,2,4,TRUE) or =ADDRESS(1,2,4) and even as =ADDRESS(1,2) and all would have returned the reference to the same cell. The last format is the one used commonly and omits any optional parameter. The result by using the last parameter would simply have been $B$1.

More often than not, the ADDRESS function is used as an intermediate step to generate a cell address and then use that as the input to the INDIRECT function. By using a combination of ADDRESS and INDIRECT functions, you can complete a full circle – starting from reading the address of the cell and then using that address to return the value stored at that address.

In the example above, we check for the accuracy of the returned address by using it as the input to in INDIRECT function. Simply write =INDIRECT(cell_where_you_entered_the_ADDRESS_Formula) and that should give you the initial value (which was the string “Mike” stored in B1 in the example)

How to enter the ADDRESS formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =ADDRESS(

3. Then enter the number which corresponds to the row of the cell whose address you are trying to generate

4. Press the comma key (,)

5. Then enter the number which corresponds to the column of the cell whose address you are trying to generate.

6. At this point, you can omit all the remaining parameters and simply close the formula by entering the closing bracket ). However if you want, you can enter the remaining optional parameters. In that case, ignore the first part of step 6.)

7. Press the comma key (,)

8. Enter a value from (1,2,3,4) as the ref_type which indicates the type of address (relative or absolute) to return. (see above for description)

9. Press the comma key (,)

10. Enter a value as TRUE or FALSE indicating the style of address you would like to return (A1 style ot R[1]C[1] style. (see above for description)

11. Press the comma key (,)

12. Enter the sheet name in which the cell is located in brackets (Ex. “Sheet1″ or as

13. Close the formula bracket by typing in the closing bracket ).

Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =ADDRESS(1,2,4,TRUE,”Sheet1″)

Possible Errors with the ADDRESS Formula

The ADDRESS formula can result in the following error values:

ADDRESS Formula #VALUE! Error

The #VALUE! error can result from a wrong value of the row or column numbers being specified or by specifying a reference type outside of 1,2,3 or 4. For example if you entered the ADDRESS formulas a =ADDRESS(1,20000,4,TRUE,”Sheet1″), it would give you the #VALUE! error because the column number has been wrongly specified. A #VALUE! error typically signifies that there is a problem with the first three parameters of the formula. If you get this error, check the row and the column number and see if they can refer to cells which are actually located on the worksheet. Then check if you’ve erroneously specified a reference type value that is not in the admissible list.

ADDRESS #NAME? Error

The #NAME? Error value in ADDRESS formula can occur when you specify the wrong type of reference style value. The reference style value can be either TRUE or FALSE (FALSE can also be specified as 0 and TRUE by any number <> 0). This error can also occur when you specify the row or the column number as anything other than a numeric value and which does not equate to a numeric value. For example =ADDRESS(1,SUM(2,0),8,1,”Sheet1″) is a valid example of the ADDRESS function but =ADDRESS(1,test,8,1,”Sheet1″) is not.

You can download an example of ADDRESS formula here or click on the button below:

download example of ADDRESS function

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Susan wrote:

    Exactly what I needed – Used to check the totals in one sheet which is continually being added to by columns, with the summary totals in another sheet (both from different data sources). I ended up with the following formula to get the cross check required. =INDIRECT(ADDRESS(2,COUNTA(Sheet1!6:6),4,TRUE,”Sheet1″)) Row 6 always contained the date, Row 2 is the total of the column – so formula finds the end column, returns the address and indirect returns the value – Wonderful – Thank you

  2. Elizabeth Apodaca wrote:

    When I download your example it works fine.
    When I add this to my workbook I get 0 instead of MIKE.
    Can you tell me why that is?

  3. Elizabeth Apodaca wrote:

    Pardon me – It works – I had the wrong Sheet name in there.

  4. luis escudero wrote:

    I need help with a little project can anyone tell me if it is possible to make an adress appear in one area by entering a number in another?For ex. if I enter 1 in row 1 column A how do I get an address to appear in row 5 column c?

  5. santhoshkumar.r wrote:

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel