1. Cell addresses
In this video, you'll see the different ways of specifying a cell's position, how to convert between them, and why you need multiple specifications.
2. Rows and columns
Throughout the course so far, you've been specifying the address of each cell using "A1" format. That is, a letter denoting the column followed by a number for the row.
Some functions that you are about to use require use you think about the column number and row number.
In this example you can see the use of the ROW() function for extracting the row part of the address as a number, and the COLUMN() function that does the same with the column part.
3. Addresses
Going in the other direction, you can use the ADDRESS() function to convert row and column numbers to addresses in A1 format.
By default, they return absolute addresses, as you can see in the third example.
There are actually four different return formats, but the only other really useful one is to return relative addresses. You can do this by passing the value 4 to the third argument of ADDRESS().
4. Indirection
There is a computer science term called "indirection", where instead of passing a value to a function, you pass an address.
The INDIRECT() function is the main user of the return value from ADDRESS(). In the first row of the example, you can see that ADDRESS() returns the absolute address of cell A2. Then in the second row, INDIRECT() takes that address, and returns a value.
5. Finding nearby cells
Rather than giving the address of a cell relative to the top left of the whole spreadsheet, it is sometimes useful to give the address relative to another cell.
To do this, you call OFFSET(), passing a reference cell, the number of cells to move down, and the number of cells to move left.
In the example, you can see that moving down three cells and right one cells from A3 returns the value in B6.
You can also use negative numbers to move up or right from the reference.
6. Relative addresses
If your dataset doesn't start at the top-left of the spreadsheet, it is sometimes easier to specify cells relative to the top-left of your dataset.
You can do this with the INDEX() function.
In the example, the range of the data is from B3 to C7. This is the first argument to INDEX(), and it should be specified using an absolute reference.
Then the cell positions can be referred to using row and column numbers relative to the top left corner, B3.
One important difference of INDEX() compared to OFFSET() is that INDEX() understands the limits of the data set's range, so if you ask for a cell outside the range, it will cause an error.
In the example, specifying a value in the third row throws an error because there are only two rows of data.
7. Summary
To summarize, cell addresses can be specified using integer positions for the row and the column, or using a letter and number in "A1" format.
The ROW() and COLUMN() functions convert in one direction, and ADDRESS() converts back.
INDIRECT() takes the address of a cell and returns the value in that cell.
OFFSET() lets you specify addresses relative to another cell.
Finally, INDEX() lets you specify addresses relative to a block of data.
8. Happy cell hunting!
Happy cell hunting!