" wrote in
message ...
Thanks Bob, I've seen a lot of your solutions and teaching. One word!
Fantastic!

For fine tuning, 3 more questions. Hope you don't mind.

Question 1. I have tried adding this, but it doesn't work.

If Cells.Count 1 Then Exit Sub


Just add this line

If .Cells.Count 1 Then GoTo ws_exit

after the line


With Target


If Len(Target.Value) 0 Then Exit Sub
What I am trying to do:
When I hit the delete button the formula would reinstate itself to the
formula coz when I hit the delete button, cell A2 must be re-entered in
order
for the formula to re-appear (it also allows for change).

For example:

1st Time
Cell A2: 200
Cell P2: 200 (Offset(0, 15).FormulaR1C1 = "=RC[-15]")

2nd Time
Cell A2: 200
Cell P2: *blank* Deleted or changed by mistake.


I don't understand what you are trying to do here, testing the len for 0
and exiting means that the cells P:V never get cleared down.


Question 2. Approximately what is the best method to use to write the R1C1
formula below (headache!), the way I wrote the formula is a part by part.

This is not a problem ="CONCATENATE(RC[-1],""-"",RC[-2])" using the macro
recorder.

This is a problem ="IF(ISBLANK(C8),"
",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8 ,"-",C8),Dbase!I:I,0))),"
",INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0))&":
"&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8, "-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B 8,"-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy"))))


In this case, I would do two things.

First, I wouldn't use R1C1, I would use A1 notation and pick up the row from
Target

and second, I would build the string up bit by bit, so that I could debug
what was happening more easily.

Something like this (I haven't got your formula correct, this is just to
show the technique

sFormula = "=IF(ISBLANK(C" & .Row & "),"
sFormula = sFormula & ",IF(ISNA(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0))),"
sFormula = sFormula & ",INDEX(Dbase!C:C,MATCH(CONCATENATE(B" & .Row & "," -
",C" & .Row & "),Dbase!I:I,0)@"
sFormula = sFormula & "):&IF(VLOOKUP(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0)@"
sFormula = sFormula & "),'Com-Max'!A:B,2,FALSE)=0," -
",TEXT(VLOOKUP(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),""mmm-yy""))))"

..Offset(0, 17).Formula = sFormula


Question 3. Is it possible to use something like this, it's much simpler
instead of R1C1 formula.

If Target.Address = "$L$6" Then Target.Formula= "=YEAR(TODAY())"
If Target.Address = "$L$12" Then Target.Formula=
"=IF(ISERROR(RIGHT(INDEX('31'!L:L,MATCH(CONCATENAT E(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0))))+1),""Not
Paid"",RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," "-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))+1))"

Yes, that is the point I was making in 2, but you have to replace the
hard-coded row number with the dynamic .row, for instance

CONCATENATE(G6,""-"",L6)

becomes

CONCATENATE(G" & Target.Row & ",""-"",L" & Target.Row & "),