circular shmircular

  • Thread starter Thread starter Cagey
  • Start date Start date
C

Cagey

I have a worksheet that tracks every sales lead that comes into my business,
with information on which rep it goes to, where the inquiry came from,
wether lead was converted to sale etc. Because of the nature of our
industry, sales are not always converted on the spot, and in fact can often
be a month before the order comes in. In the sheet there is a column that
rep enters "y" for ordered, "n" for declined/ordered elsewhere and leave it
blank if undecided. What i want to do is automatically change any blanks to
an "n" after 60 days. I came up with this where C1 is y/n cell and B1 is the
result of the fomula to add 60 days to the original date the lead came in.
=IF(C1="n","n",(IF(C1="y","y",(IF(B1<TODAY(),"n","u")))))
but i have to have it in a different cell obviously to prevent circ ref. I
want the result to go into C1....
Please help.
 
Cagey,

Reading your post, I interpret the logic a little
differently.

Assume B1 has contact date, say 25/9/03 (or 9/25/03 if
you are from that part of the world... end of september
anyway).
in C1, enter =IF(B1+60<TODAY(), "n", "u")and let the
Sales people over ride the formula if/when they need to.

So, 25/9 is ,more than 60 days ago, formula displays "n",
if date was 1/11, less than 60 days, display is "u".

Salesman comes along looks up the contact, if order
placed enters "y", formula overwritten, no dramas,
Follow up made, customer buying elsewhere, salesman
enters "n", no dramas.

Put another formula in D1 =If(Len(c1)>1, "", "Follow up
complete" and you begin giving hints to your sales team
of leads to follow.


steve
 
Back
Top