Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbering system
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbering system
Hi Majestyc, Try =IF(D3="","",rank(D3,$D$3:D1000,1)) H S Shastri ========================================= "majestyk" wrote: 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: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbering system
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Majestyc, Try =IF(D3="","",rank(D3,$D$3:D1000,1)) H S Shastri ========================================= "majestyk" wrote: 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: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbering system
You would hit problems with this if two date in D were the same.
Paul "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Majestyc, Try =IF(D3="","",rank(D3,$D$3:D1000,1)) H S Shastri ========================================= "majestyk" wrote: 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: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbering system
On 16 Feb, 05:16, Shane Devenshire
wrote: 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "HARSHAWARDHAN. S .SHASTRI" wrote: * Hi Majestyc, * *Try * =IF(D3="","",rank(D3,$D$3:D1000,1)) H S Shastri ========================================= "majestyk" wrote: 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: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I create a sequential numbering system on an Excel document? | Excel Discussion (Misc queries) | |||
how do I increment a numbering system in excel | Excel Discussion (Misc queries) | |||
Auto Numbering System | Excel Worksheet Functions | |||
How can I keep my numbering system consecutive when I sort? | Excel Discussion (Misc queries) | |||
How do I set up an automated numbering system For my "Invoice Num | Excel Worksheet Functions |