• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating unique number with Text and numbers

  1. Hi:

    In Cell A1, I have a drop down with potential 2 values (1) Issues (2) Task. In Cell B1, I would like following values to be created: when A1 is "Issues" generate the value "ISS-001" (in B1). If "Task" is selected in Cell (A1) then the value in B1 should be "TSK-001". The value in the oth "A" cells (Issue or Task) will follow the same numbering scheme.
  2. The challenge is when the user changes the value in, let's say, A5 from "Task" to "Issue". I would like to generate the next sequential for "Issue" and never to reuse the "Task" number that was there.
  3. So let's say in A5 there was "Task" and B5 contained "TSK-003", the the next newly created "Task" entry should be "TSK-004" (I do not want to ruse "TSK-003".


  4. So I created this formula B1 thru B7 (I will increment it as the number of Tasks and Issue will increase:

    =IF(A1="Issue",CONCATENATE("ISS-",TEXT(COUNTIF(A2:A7,"ISSUE")+1,"000")),IF(A1="Task",CONCATENATE("TSK-",TEXT(COUNTIF(A2:A7,"TAS*")+1,"000"))))

    There is a problem with this formula. The numbers in B1-B7 change as I increment the use of the cell i.e. A1 = Task, A2 = Issue A3=Issue A4 = Task etc. Also if I change a "task" row3 to a "issue" row, the numbers also change on the entire set. How can I ensure that the values in the B1-B7 cells, once determined by the formula, do NOT CHANGE?

    Thanks in advance.

    frank
 
Hi Frank ,

Your explanation is detailed , but the application is more complicated than your explanation reveals.

Can you upload a sample workbook , with say 10 to 20 rows of data , consisting of enough IDs using the TSK prefix , and enough IDs using the ISS prefix.

Also explain with at least 2 examples , what would happen if any Task were to be changed to Issue , and the other way round.

Just as an example , suppose the entries were as follows :

TSK-001
TSK-002
ISS-001
TSK-003
ISS-002
ISS-003
TSK-004

Is this a valid illustration of the way entries will be present ?

If so , what would happen if the entry TSK-003 were to be changed to Issue ?

Thereafter , suppose the entry TSK-002 were to be changed to Issue , what would be the result ?

Narayan
 
Narayan:

Thanks. Attached is an example. Please let me know if you need additional information. Thanks.

frank
 

Attachments

  • ISS_TSK_EXAMPLE.xlsx
    9.9 KB · Views: 0
Narayan:

Thanks. Attached is an example. Please let me know if you need additional information. Thanks.

frank

Narayan:

Hi, to answer you question above.

  • Is this a valid illustration of the way entries will be present ? - Yes. This is a valid representation of the results that would be generated when "Issue" or "Task" is selected
  • What would happen if the entry TSK-003 were to be changed to Issue ? - A new "Issue" number would be generated - "ISS-004". Note: "TSK-003" would never be reused.
  • Additionally, if later "ISS-004" was changes to an Task then the ID would be "TSK-005"
  • Thereafter , suppose the entry TSK-002 were to be changed to Issue , what would be the result ? - Then a new "Issue" number would be generated - "ISS-005".
    The premise is that once an ID has been assigned, it is never reused, and the next sequential number is generated based on the selection of "Issue" or "Task"
Does this make sense? Thanks.
frank
 
Hi:

Find the attached. I have used a combination of codes and formulas to achieve what you were looking for. I am not sure whether this is an ideal solution but it sure does work to your specifications.

Thanks
 

Attachments

  • ISS_TSK_EXAMPLE.xlsm
    18.7 KB · Views: 0
Hi Frank ,

See the attached file.

Narayan

Hi Narayan:

Based on my description on what I wanted to accomplish, the so0lution is perfect. There is, however, a small issue. Let me explain: If there is a row with the following: And the user uses the dropdown and select "Task" again (in the same row), the ID increases to TSK-009 and if he does it again (without going to a new cell), the ID increases to TSK-010. Check it out....Thanks...Frank
Task TSK-008
 
Perfect - :cool: Thanks Narayan!!!!

Narayan:

I 'cut and paste' the VBA code from your example into my 'ultimate' excel worksheet and am now getting a "Run Time Error '424' Object Required". I am extremely new to VBA not sure what I excluded or did incorrectly. Can you point me in the right direction? Thanks & the file is attached.
 

Attachments

  • Issue_Task_Manage_FB.xls
    52.5 KB · Views: 0
Hi Frank ,

I have defined 3 named ranges , which will need to be created if the code is transferred to any other workbook.

The code itself has 3 parts to it :

1. Sheet section code
2. ThisWorkbook section code
3. Module section code

Narayan
 

Attachments

  • Issue_Task_Manage_FB.xls
    59 KB · Views: 2
Hi Frank ,

I have defined 3 named ranges , which will need to be created if the code is transferred to any other workbook.

The code itself has 3 parts to it :

1. Sheet section code
2. ThisWorkbook section code
3. Module section code

Narayan

Narayan:

Thanks so muck. I really appreciate it.

frank
 
Hi Narayan:

One last question: In the name ranger there is a name range call OLE_LINK1=Issues_Tasks_Tracking_Log!#REF!

I am not sure what this is referring to and as you can see the reference is invalid in the above. Please advise.

frank
 
Hi Frank ,

See if this works correctly.

Narayan

Hi Narayan:
One last question: In the name ranger there is a name range call OLE_LINK1=Issues_Tasks_Tracking_Log!#REF!
I am not sure what this is referring to and as you can see the reference is invalid in the above. Please advise.
frank
 
Hi Frank ,

I have not added this named range , and I cannot say what it refers to ; however , since it is no longer valid , you can certainly delete it without any consequences.

Narayan
 
Back
Top