numbering system

[majestyk]

I am trying to create a membership number based upon a date of
payment. Potential clients names are collected and entered into
column
B. The date of their first presentation is column C. The date of
their
membership payment (most important as they are NOT allocated a number
until complete) is colum D. The membership number is allocated in
column A and is based upon a date entered into D. I have so far:

IF(D3="","",COUNT($A$1:A2)+1)). This creates a sequential number BUT
changes when a member listed above in order makes a payment. Is there
a way to allocate a static number to a column with reference to
another cell?
thank you

 

[HARSHAWARDHAN. S .SHASTRI]

Hi Majestyc,

Try
=IF(D3="","",rank(D3,$D$31000,1))

H S Shastri

=========================================

 

[Shane Devenshire]

Hi,

I think that will still change if a date is entered in an earlier item in
column D. Or at least it could change.

Question for the OP? Are the dates in column D always entered in
chronological order. that mean if person 1 is 1/1/2009 and person 2 is blank
and person 3 is 12/1/2008 when you enter person 2's date is it always going
to be later than the previous dates or could you later put in 12/15/2008.

Actually any formula you choose which is based on the date in column D will
be potentially non-static. Suppose a persons check bounced and you need to
change their date, or that a date entered much earlier was incorrect and now
required changing?

To make a static numbering system it might be better to choose VBA rather
than a formula. By definition formulas are dynamic.

 

[Paul]

You would hit problems with this if two date in D were the same.

Paul

"HARSHAWARDHAN. S .SHASTRI"

 

[majestyk]

Hi,

I think that will still change if a date is entered in an earlier item in
column D.  Or at least it could change.  

Question for the OP? Are the dates in column D always entered in
chronological order.  that mean if person 1 is 1/1/2009 and person 2 isblank
and person 3 is 12/1/2008 when you enter person 2's date is it always going
to be later than the previous dates or could you later put in 12/15/2008. 

Actually any formula you choose which is based on the date in column D will
be potentially non-static.  Suppose a persons check bounced and you need to
change their date, or that a date entered much earlier was incorrect and now
required changing?

To make a static numbering system it might be better to choose VBA rather
than a formula.  By definition formulas are dynamic.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire








- Show quoted text -

Shane,
thank you. I had suspected that code would be the only way to do this.
I have tried to write a formula that checks the existing column, finds
the last number then adds 1, but I get a circular reference all the
time. If anyone is able to steer me towards any pre written code, I
would appreciate it.

regards
Jonathan