Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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! . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
name deletion | Excel Discussion (Misc queries) | |||
simple row deletion | Excel Discussion (Misc queries) | |||
Accidental Deletion | Setting up and Configuration of Excel | |||
On deletion of rows | Excel Discussion (Misc queries) | |||
#REF! after cell deletion | Excel Discussion (Misc queries) |