Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Address cell in Excel

Return address of the cell and use the cell address 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 cell address is often used when we use Excel INDIRECT function. In the formulas of this tutorial is often used Excel functions ADDRESS.

You note that function ADDRESS 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.
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 cell address 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.
Return address cell

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 functions ADDRESS can reference a specific cell address and use it in a formula when copying formulas.

Figure 2.
ADDRESS function in Excel

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 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.
Return address cells in Excel

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,
D1 column 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 function ROW.
=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.
Increase Column Letter in Excel
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 column (column heading) depending on the number of rows in which the formula is

=CHAR(64+ROW())

Display address last edit 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.
Display address last edited cells

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.
Referencing address cells in Excel

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 example and in this way we can solve the complicated requirements. See tutorial function INDIRECT.
Also see the previous tutorial related to this topic: How to display or fixed letter columns in Excel

Attention! You may need to use a comma instead of a semicolon. Source idea: Internet network