View original (French)

EXCEL - generic formula cell above?

Solved
bichette -  
 Vincent -
Hello,

in my table, in column A, I put line numbers. For now, I have the simple formula, for example in A4:

=A3+1

and then I dragged the formula down the whole column.

The problem is that if I delete an entire row, I end up with #REF!, of course.

Do you know a formula that says something like:

"above cell" +1

Is there some kind of trick with ADDRESS or INDIRECT???

Thanks!
Configuration: Windows XP Firefox 3.0.11

8 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    If your starting cell is A3
    you place the formula in A4 and drag it down

    the cells will increment by 1
    since
    INDIRECT("A"&LIGNE()-1) gives you the value of the cell above
    INDIRECT("A"&LIGNE()-1)+1 adds 1 to the value of the cell above, which is what you requested
    you can thus delete a line from line 4 to X
    --
    Best regards, Michel
    13
    1. Vincent
       

      By keeping the same logic, is it possible to have the same formula in the case where there is one or more empty lines between the two?

      0
  2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello sweetheart
    =INDIRECT("A"&ROW()-1)+1
    --
    Best regards, Michel
    2
    1. Atlas
       
      Thank you very much, Michel!!
      1
  3. bichette
     
    thank you michel for the explanation

    i adapted the formula: =INDIRECT("B"&ROW()-1)+1

    it works well when I drag it down.

    but when I delete a row I still get the REF!

    arrgghhh
    2
  4. Sooo
     
    Hello,

    What if we want to select the cell below?
    My references are arranged in order and I would like it to take the cell below the given one.

    Thank you
    2
  6. bichette
     
    Thank you, Michel.

    But I don't know how to use your formula. Can you give an example?

    If I put 1 as "hard" in cell A3 and I put your formula in A4.

    Thanks again for your help.
    0
  7. bichette
     
    noooooo sorry wrong manipulation

    EVERYTHING WORKS!!!

    Thank you Michel!!!
    0
  8. bichette
     
    That said, it works well, but EXCEL puts a green triangle in the cells and displays "inconsistent calculated column formula."

    What's the point???
    0
  9. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    If your starting cell is A3
    you put the formula in A4 and drag it down

    the cells will increment by 1
    since
    INDIRECT("A"&LIGNE()-1) gives you the value of the cell above
    INDIRECT("A"&LIGNE()-1)+1 adds 1 to the value of the cell above, which is what you asked for
    you can thus delete a line from line 4 to X
    --
    Best regards, Michel
    0