Working With Address Cell In Excel
Return Address of the Cell and use the Address Cell in the Formula
In this tutorial, I want to show that we can display the address of the cell or to use a cell address in the formula when copying a formula to the right or down. For further details of how to display the Column Letter, see the tutorial at the link.
I believe that you know what is absolute and what is relative to a cell, if you do not know, see the respective link.
Referencing a Address Cell is often used when we use Excel INDIRECT function. In the formulas of this tutorial is often used Excel ADDRESS functions.
You note that ADDRESS function has syntax
ADDRESS(row_num;column_num;abs_num;a1;sheet_text)
- row_num: is the line number to be used in the address of the cell.
- column_num: is the number of columns to be used in the address of the cell.
- abs_num: specify the type of address that is returned.
Returns this types of Addressess
abs_num |
Returns this type of address |
---|---|
1 or omited |
Absolute address |
2 |
Absolute row; relative column |
3 |
Relative row; absolute column |
4 |
Relative address |
In the picture below you see the Address Cell in the formula when copying to the right.
Also note that all formulas begin in column 'B'. Example of Absolute and Relative Address Cell here.
Figure 1.
The formula in cell B8 is the following:
=ADDRESS(2;COLUMN(A1);4)
The formula in cell B9 is the following:
=ADDRESS(2;COLUMN(A1);3)
The formula in cell B10 is the following:
=ADDRESS(2;COLUMN(A1);2)
The formula in cell B11 is the following:
=ADDRESS(2;COLUMN(A1);1)
The formula in cell B12 is the following:
=ADDRESS(1;COLUMN(A1);4)
The formula in cell B13 is the following:
=ADDRESS(4;COLUMN(A1);4)
The formula in cell B14 is the following:
=ADDRESS(7;COLUMN(A1);2)
In the picture below you will see how to use formulas and ADDRESS functions can reference a specific cell address and use it in a formula when copying formulas.
Figure 2.
The formula in cell B3 is the following: So if nestled COLUMN() function within the ADDRESS function, then we have a Dynamic Formula that changes the results when copying a formula to the right.
=SUBSTITUTE(ADDRESS(1;COLUMN(AD1);1);"$1";"")
The formula in cell B7 is the following:
=SUBSTITUTE(ADDRESS(1;COLUMN(AD1);2);"$1";"")
The formula in cell B11 is the following:
=SUBSTITUTE(ADDRESS(1;COLUMN(AD1);3);"$1";"")
The formula in cell B15 is the following:
=SUBSTITUTE(ADDRESS(1;COLUMN(AD1);4);"$1";"")
The formula in cell B19 is the following: So if we use the ROW() function then we have a dynamic formula that changes the results when copying a formula down. If we set ROW(A5) then result formula returns the address AD5.
=SUBSTITUTE(ADDRESS(ROW();COLUMN(AD1);4);"$";"")
Return the Absolute Address of a Specific Cell in Excel
In the picture below you will see the results of that formula. Different Absolute and Relative Cell Addresses. Attention, all formulas are in the B column. Notice how the score when copying a formula.
Figure 3.
The formula in cell B1 is the following: You notice that the place of argument row_num K1, Column K is equivalent to the number 11 (number of the column).
=ADDRESS(COLUMN('Letter-Sheet'!K1);COLUMN('Letter-Sheet'!H1))
The formula in cell B5 is the following:
=ADDRESS(COLUMN('Letter-Sheet'!A1);COLUMN('Letter-Sheet'!R1))
The formula in cell B9 is the following: Note that in place of argument row_num K9, Column K is equivalent to the number 11 (number of the column). Also note that at the point of argument column_num D1, Column D1 is equivalent to the number 1 (number rows) and the result is a column that is the first in a series. So the result is the address of the cell A11.
=ADDRESS(COLUMN('Letter-Sheet'!K9);ROW('Letter-Sheet'!D1))
The formula in cell B13 is the following: This dynamic formula, unlike the previous, return cell addresses when copying down. This is because we use as arguments the ROW function.
=ADDRESS(ROW('Letter-Sheet'!A1);ROW('Letter-Sheet'!D5))
Increase Letter to a Certain Number of Columns when Copying a Formula
(Increase Column Letter)
If you need to reference the formula to a specific cell address to be Increased by a certain number of columns when Copying a Formula, see the example in the figure below.
Figure 4.
The formula in cell A1 is the following: Copy the formula to the right. This formula applies only to letter columns from A to Z
=CHAR(64+COLUMN()+COLUMN(K3))
The formula in cell D3 is the following: Copy the formula down
=SUBSTITUTE(ADDRESS(1;ROW(A1)+ROW(A4);4);"1";"")
The formula in cell E3 is the following: Copy the formula down
=SUBSTITUTE(ADDRESS(1;ROW(A1)+ROW(A5);4);"1";"")
Display Column Letter Depending on the Line Containing the Formula
This formula shown below letter of the Column (Column Header) depending on the number of rows in which the formula is
=CHAR(64+ROW())
Display Address Last Edited Cell (Return Last Entered Cell)
If you want to know in real time, which was last edited cells then use the formula below
Figure 5.
The formula in cell C29 is the following: Copy the formula down
=SUBSTITUTE(CELL("ADDRESS");"$"&ROW();"")
The formula in cell C31 is the following: Copy the formula down
=SUBSTITUTE(SUBSTITUTE(CELL("ADDRESS");"$"&ROW();"");"$";"")
The formula in cell C33 is the following: Copy the formula down
=MID(CELL("ADDRESS");2;FIND("$";CELL("ADDRESS");2)-2)
Referencing Address Cells in Excel
At the beginning of this tutorial, I mentioned that Referencing the Cell Address we can use the formula using Excel INDIRECT functions.
Figure 6.
In the picture above you see an example of how we can show the result of a Cell from another worksheet if the Worksheet Name and address of the cell are entered as Data. So, in the picture above we have the names worksheets in column A, column B are cell addresses from which we need to return a result.
The formula in cell E1 is the following: Copy the formula down
=INDIRECT(A1&"!"&B1)
The formula in cell F4 is the following: Copy the formula down
=INDIRECT(A4&"!"&B4)
This is a very simple Excel example and in this way we can solve the complicated requirements. See tutorial INDIRECT function.
Also see the previous tutorial related to this topic: How to display or fixed Columns Letter in Excel. Source idea: Internet Network.
Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.