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

Answer 1 / 8
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
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
Answer 2 / 8
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello sweetheart
=INDIRECT("A"&ROW()-1)+1
--
Best regards, Michel
2
Atlas
 
Thank you very much, Michel!!
1
Answer 3 / 8
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
Answer 4 / 8
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
Answer 5 / 8
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
Answer 6 / 8
bichette
 
noooooo sorry wrong manipulation

EVERYTHING WORKS!!!

Thank you Michel!!!
0
Answer 7 / 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
Answer 8 / 8
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