#Ref! after row deletion
 

How can one stop the #Ref! from appearing after deleting row from above? Exam:
Col A = Date field
Col B = Data field
Col C = Reference from Col B, formula = if(A2="",C1,B2) Etc. to row 10.
Row C11 contains formula =C10

delete rows 3,4,5 leaves row 11, now row 8 with #Ref!

What kind of formula can stop the #Ref!

#Ref! after row deletion
 

One option is to use Indirect to refer to the previous row, as in:
=Indirect("A"&row()-1)

Regards,
Fred

"Rick" wrote in message
...
How can one stop the #Ref! from appearing after deleting row from above?
Exam:
Col A = Date field
Col B = Data field
Col C = Reference from Col B, formula = if(A2="",C1,B2) Etc. to row 10.
Row C11 contains formula =C10

delete rows 3,4,5 leaves row 11, now row 8 with #Ref!

What kind of formula can stop the #Ref!

#Ref! after row deletion
 

Thanks Fred it works well except for date fields. What is returned is
1901/01/00.....
I use to use If(A10="","",A9)

"Fred Smith" wrote:

One option is to use Indirect to refer to the previous row, as in:
=Indirect("A"&row()-1)

Regards,
Fred

"Rick" wrote in message
...
How can one stop the #Ref! from appearing after deleting row from above?
Exam:
Col A = Date field
Col B = Data field
Col C = Reference from Col B, formula = if(A2="",C1,B2) Etc. to row 10.
Row C11 contains formula =C10

delete rows 3,4,5 leaves row 11, now row 8 with #Ref!

What kind of formula can stop the #Ref!

.

#Ref! after row deletion
 

A date field cannot return 1901/01/00. If the number 0 is formatted as a
date, then you will see 1900/01/00. If you want blank instead, you will have
to modify your formula in the same way, as in:

=if(indirect("A"&row()-1)="","",indirect("A"&row()-2))

Regards,
Fred






"Rick" wrote in message
...
Thanks Fred it works well except for date fields. What is returned is
1901/01/00.....
I use to use If(A10="","",A9)

"Fred Smith" wrote:

One option is to use Indirect to refer to the previous row, as in:
=Indirect("A"&row()-1)

Regards,
Fred

"Rick" wrote in message
...
How can one stop the #Ref! from appearing after deleting row from
above?
Exam:
Col A = Date field
Col B = Data field
Col C = Reference from Col B, formula = if(A2="",C1,B2) Etc. to row
10.
Row C11 contains formula =C10

delete rows 3,4,5 leaves row 11, now row 8 with #Ref!

What kind of formula can stop the #Ref!

.