Menu
In a worksheet function in Excel, how do you self-reference the cell, column or row you're in?
808
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.
Click the cell in which you want to enter the formula. In the Formula Bar, type = (equal sign). Do one of the following: Reference one or more cells To create a reference, select a cell or range of cells on the same worksheet.
It is a built-in function and takes only one argument as the reference. The method to use this function is as follows: =ROW( Value ). It will only show the cell's row number, not its value. For example, =ROW(A5) returns 5 because A5 is the fifth row in the Excel spreadsheet.
Now there are three kinds of cell references that you can use in Excel: Relative Cell References. Absolute Cell References. Mixed Cell References.
For a cell to self-reference itself:
INDIRECT(ADDRESS(ROW(), COLUMN())) For a cell to self-reference its column:
INDIRECT(ADDRESS(1,COLUMN()) & ":" & ADDRESS(65536, COLUMN())) For a cell to self-reference its row:
INDIRECT(ADDRESS(ROW(),1) & ":" & ADDRESS(ROW(),256)) or INDIRECT("A" & ROW() & ":IV" & ROW()) The numbers are for 2003 and earlier, use column:XFD and row:1048576 for 2007+.
Note: The INDIRECT function is volatile and should only be used when needed.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With