# How to reference a cell by location instead of cell name in Excel

Hopefully this is a very basic question. There may be a simpler way to solve my problem, so I'll go over that first then ask the question I'm trying to ask.

My problem: I want A1=0 A2=1 A3=2 ... A500=499

My thought is to do something like "=[A:This-1]+1" where This is the number of the cell I'm currently working in. Is there a 'This' variable equivalent? Or some way to say always use the cell above instead of having to go through all 500 cells and count?

• `=ROW(A1)-1` and populate down – cybernetic.nomad Sep 18 '18 at 21:24
• @cybernetic.nomad - you can save a byte or two with `=ROW()-1`. Edit: Or, in `A1`, just enter `0`, then in `A2`, `=A1+1` and drag down. Or... – BruceWayne Sep 18 '18 at 21:29
• @BruceWayne - never knew that one! -- thanks! – cybernetic.nomad Sep 18 '18 at 21:32
• @BruceWayne I could be wrong, but I believe `ROW()` is a volatile function. Apparently it depends what web page you reads to whether or not ROW() is a volatile function. – Forward Ed Sep 18 '18 at 21:41

To get something similar to `=[A:This-1]+1`, try using `=INDIRECT("A"&ROW()-1)+1`
`ROW()` will give you the row number of the cell that the formula is in (15 if the formula is in B15) and subtract 1 (14). `INDIRECT` will then get the value in A14 and add 1 (25 if the value in A14 is 24).
If you need to also do relative math on the column, you can use the `ADDRESS` function like so:
``````=INDIRECT(ADDRESS(ROW()-1,COLUMN()-1,4,1)+1