0

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?

  • 2
    =ROW(A1)-1 and populate down – cybernetic.nomad Sep 18 '18 at 21:24
  • 1
    @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
  • 2
    @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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.