This allows the formula to be copied to another column and still work.
With INDIRECT and ADDRESS
Another approach is to use a formula based on theINDIRECTandADDRESSfunctions.
OFFSET is handy in formulas that require a dynamic range.
For example, =ADDRESS(1,1) returns $A$1.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
Related videos
How to use the COUNTIFS function
