+ Reply to Thread
Results 1 to 47 of 47

alphanumeric formula help

  1. #1
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    alphanumeric formula help

    Hi all,

    I was first using the formula:

    =IF(OR(D8="S1", D8="S2", D8="S3"), "PSC-S-"&TEXT(ROWS($1:1),"0000"), IF(OR(D8="T1", D8="T2", D8="T3"), "PSC-L-"&TEXT(ROWS($1:1),"0000"),""))

    Where D8 is a drop down list. I then started to use the formula below in sheet 2 so that the last result from sheet1 would be carried over.

    =IF(OR(D8="S1", D8="S2", D8="S3"), "PSC-S-"&TEXT(ROWS($1:1)+MATCH(REPT("z",255),Sheet1!$D$8:$D$27),"0000"), IF(OR(D8="T1", D8="T2", D8="T3"), "PSC-L-"&TEXT(ROWS($1:1)+MATCH(REPT("z",255),Sheet1!$D$8:$D$27),"0000"),""))

    When i try to use this formula in sheet3, by replacing sheet1! with sheet2! this does not work?

    Also, my formula is incrementing down the cell (see result in black). i.e

    S1 PSC-S-0001 PSC-S-0001
    T1 PSC-L-0002 PSC-L-0001
    S2 PSC-S-0003 PSC-S-0002
    S3 PSC-S-0004 PSC-S-0003
    S2 PSC-S-0005 PSC-S-0004
    T3 PSC-L-0006 PSC-L-0002




    The text in red is what i would like the result to be i.e If i select S1, S2 or S3 for the first time i get PSC-S-0001, then the second time it should be PCS-S-0002 even if the second time was way down in cell 15 (at the moment if the second time i selected either S1, S2 or S3 down in cell 15 it would give the result PSC-S-0015)

    The incrementing for an S code should be independant to an L code. How can i achieve this as well making this work in another sheet by looking at the last S code number and or L code number.

    I attached a link, which will give a brief example of the what i am trying to achieve (expected result column) and what is happening at the moment.

    https://www.sendspace.com/file/yojtr1

    Thank you in advance for your kind help.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: alphanumeric formula help

    Welcome to the forum!

    Please attach the workbook HERE - some members are unable or unwilling to access file-sharing sites.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Please find attached workbook for those who cannot access the link.

    Thank you.
    Attached Files Attached Files
    Last edited by AliGW; 12-31-2017 at 01:18 PM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: alphanumeric formula help

    Thank you.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Try this in sheet1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In other sheets you can use same formula only you need to add part for counting previous sheets:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    thank you zbor, that works exactly how i wanted it to, much appreciated for your help and speedy response :-)

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: alphanumeric formula help

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  8. #8
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    @zbor

    just out of curiosity and for future reference. If i wanted to start the first PSC-S- code as PSC-S-0800 and the first PSC-L- code say as PSC-L-0600, how i would i be able to do that? rather than both starting from 0001.
    Last edited by AliGW; 01-01-2018 at 11:51 AM. Reason: Unnecessary quotation removed.

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    add 799 + 1st instance will be 800 to start counting from same number.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Or with IF function determine how much to add:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, thank you for your help so far. The workbook that i'm currently working is an on going development. I've attached the same workbook as last time but this time i have completely updated my drop down list and next to it i have given the letter coding i require for each list selection i.e if i select BRP(RC) code will be PSC-L-(followed by number i.e 0001) etc

    I want it to work as before but with this additional drop down list and codes. Also can i also use the same IF function above to start these code numbers from where ever i want i.e PSC-S-0800, PSC-L-0600, PSC-J-0450 etc.

    Thanks again for your help and patience
    Attached Files Attached Files

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    How about this.

    Define names in one sheet (Names) with starting numbers.

    Then you can use formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, almost what i am after. I've attached the workbook again. i.e if all the L codes can start from the same number (600), same with the S codes (800), J codes (500) etc.

    Then on sheet 1 i have put in red as an example how i would like the codes to appear. If that works then can i still use +COUNTIF(Sheet1!$D$8:$D$27,LEFT(D8,1)&"*") to continue this over sheet2, 3, 4.....etc?

    Thanks.

  14. #14
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    I've COUNT them by E column, not D.

    And got:

    Please Login or Register  to view this content.
    Also, where is 802 in your case?

    Please Login or Register  to view this content.
    Edit: I've set also in sheet2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here is also explanation:

    1. determine First part of the word PSC-L or PSC-S etc depending on defined letter code
    2. Add starting number
    3. Count how many PSC-? exists in data above (that's why ranges are increasing in current sheet E$7:E7)
    4. count how many PSC-? exists in previos sheet (that's why whole range is taken from previous sheet Sheet1!$E$8:$E$27)

    In the next sheet you would need to add one more COUNTIF that would count from sheet2 etc
    Attached Files Attached Files
    Last edited by zbor; 01-02-2018 at 07:50 AM.

  15. #15
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, thank you for all your hardwork and patience that worked perfectly as i had wanted it to (also thank you for the explanation)

  16. #16
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi Zbor, how are you?

    The alphanumeric formula you done for me is great.

    =IF(D8="","", "PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-" &TEXT(VLOOKUP(D8,Names!$A$1:$C$39,3,FALSE) +COUNTIF(E$7:E7,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*") +COUNTIF(Sheet1!$E$8:$E$27,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*") ,"0000"))

    I'm looking to expand my workbook to have days/date at the bottom tabs which might go up to 6 months, thus there will approx 180 worksheets. Is there a quick way to do +COUNTIF(Sheet1!$E$8:$E$27,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*") for this many sheets. Rather than adding this each time with the previous sheet number.

  17. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    A little bit complicated but try this...
    First sheet is same as before.
    From 2nd sheet to the last you have one formula.

    Only thing you need is to have data in same cells,
    And sheets need to be named with numbers at the end in sequential order: Sheet1, Sheet2, .... Sheet101 or Anything1, Anything2... up to 999

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    that seems like it works. I was trying to incorporate this into my master workbook where the code reference cell starts in column N, so thought it would be just a case of changing cell references. I tried this in the test spreadsheet but in sheet 2 it wouldn't add up properly. I have attached the test spreadsheet again showing my work in column N but without getting the correct codes to appear. Would you be able to show me this working in column N? and if you are able to provide and explanation for how the formula works that would be brilliant.


    Many thanks again.
    Attached Files Attached Files

  19. #19
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Everthing is fine with your formula.
    I forgot to mention that formula need to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around your formula.

    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  20. #20
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Actually, you need to change everything from E to N column:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Confirmed with ctrl+shift+enter (not just enter)

  21. #21
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    I tried that but still when copying this into my master copy workbook, its not returning the desired results.

    =IF(D8="","","PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-"&TEXT(COUNTIF(N$7:N7,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*")+MAX(IF(LEFT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N50"),6)="PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-",--RIGHT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N50"),4)))+1,"0000"))

    the parts hightlighed in red ("filename", A1) do i need to do something with this to make it work in my workbook. I have no information in cell A1 or $A$1:$A$3. I have just copied the formula directly and pasted it and confirming with crtl+shift+enter.

  22. #22
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Nope. Those cells remains same.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    strange i keep getting the value return to PSC-L-0001 the first time i enter that formula into my own workbook.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: alphanumeric formula help

    Are you array entering the formula? CTRL+SHIFT+ENTER?

  25. #25
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    This is what i have been using in my workbook in the 2nd sheet, but its still returning the first code PSC-L-0001

    {=IF(D8="","","PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-"&TEXT(COUNTIF(N$7:N7,"PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-*")+MAX(IF(LEFT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N50"),6)="PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-",--RIGHT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N50"),4)))+1,"0000"))}

    The Names! range in my actual workbook is slightly bigger but have input it in the same place as the sample workbook.

    ROW($A$1:$A$3) what information is contained here?

  26. #26
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    If it's bigger than N50 then you need to fix range:

    {=IF(D8="","","PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-"&TEXT(COUNTIF(N$7:N7,"PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-*")+MAX(IF(LEFT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N200"),6)="PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-",--RIGHT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N200"),4)))+1,"0000"))}


    Put number more that you can reach in longest sheet but don't put too much because it can become slow.

    If this is not the case then please upload example workbook but change your private dana. Leave only dummy dana important for example.

  27. #27
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Quote Originally Posted by defenders View Post
    ROW($A$1:$A$3) what information is contained here?
    It extract last 1, 2 or 3 numbers of the sheet name (that's why limit is 999).

  28. #28
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    I think i know what is causing this to happen, but i don't know how to fix this.

    sample worksheet the formula:

    {=IF(D8="","","PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-"&TEXT(COUNTIF(N$7:N7,"PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-*")+MAX(IF(LEFT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N200"),6)="PSC-"&VLOOKUP(D8,Names!$B$21:$D$66,2,FALSE)&"-",--RIGHT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N8:N200"),4)))+1,"0000"))}

    In the sample worksheet i have been using the code starting PSC, but in my actual workbook the start code is longer PSC followed by 4 letters. i.e PSCXXXX

    so when using PSCXXXX replacing PSC in the formula its causing an issue.

    How do i rectify this?
    Attached Files Attached Files
    Last edited by defenders; 01-05-2018 at 03:59 PM.

  29. #29
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Also i know i am likely to approach this problem soon.

    you stated that the sheets need to be named with numbers at the end in sequential order: Sheet1, Sheet2, .... Sheet101 or Anything1, Anything2... up to 999

    how i can make this work if sheets are going to be dated ie. 05/01/18, 06/01/18, 07/01/18... etc

    would i have to do 05/01/18 (1), 06/01/18 (2), 07/01/18 (3)...etc?

  30. #30
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    You can name them for example 20180105sometexthere1 (number-word-sequential number without spaces)
    Last edited by zbor; 01-06-2018 at 05:28 AM.

  31. #31
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Yes, that's the problem.

    LEFT formula will look only for 6 characters.. Not 10..

    You should be more clear on your demands because logic is based on it.

    Plus your formula still looking in E column of previous sheet instead of N. (this doesn't influence your result because at the moment you have same dana in E column).

  32. #32
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    point noted and once again thank you for your help, time and patience.

  33. #33
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, I've got a simple macro to create the sheets.

    from sheet3 01/01/2018Page1 upto 12/01/18Page12. I have hidden the first 3 sheets. So the sequence starts Sheet3, 04/01/18Page4, 05/01/18Page 5...etc

    I'm find after page 5 the formula is giving me a #REF! error. Any idea why?

    I've attached the worksheet for you. Thanks
    Attached Files Attached Files

  34. #34
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Ah, my fault...
    Text part of the sheet names must be same..
    Page1, Page2, Page3...

    But since you are using macro I can set you also to inlcude your PSC naming...
    But need more detail how they are set...

  35. #35
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    i'm not too good with macros so want to keep the VB part to a minimum if possible. I tried to rename sheet 1, sheet2 and sheet3 to Page1, Page2, Page3 and then the next pages after would be 04/01/18Page4, 05/01/18Page5, 06/01/18Page6 etc, still not working.

    I also ran the macro so instead of page1, page2, page3 after the date it would be sheet1, sheet2, sheet3,....i.e 04/01/18Sheet1, 05/01/18Sheet5 etc, but still getting the same problem.

    Is there a quick solution to this?

  36. #36
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: alphanumeric formula help

    Not 06/01/18Page6.
    Only Page6

    samename123


    One of the options is that in each sheet (i.e. cell N1) you enter name of previous sheet.

    That would make your formula shorter. Plus they don't depend on sheet names.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    ok i see what you mean now about just page(number)

    So the formula above i should start in sheet2? and then put Sheet1 (or any other name) into N1? My workbook is going to have sheets for the whole year so there will many sheets. In N1 is there a formula to automatically put the previous sheet name in, that way i don't have to do this for 365 sheets.

  38. #38
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, if this is the macro i'm using, how would i incorporate the formula into it?

    Please Login or Register  to view this content.
    Last edited by zbor; 01-08-2018 at 05:55 AM.

  39. #39
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, i tried this in the test workbook, but can't seem to get it to work. Do you know what i am doing wrong? I've attached the test workbook that's using the above code

  40. #40
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, tested this up to six pages/dates. Upto page 3 it works but then after having the same problem where the cells show #REF.

    I have attached the workbook.

    Do you know why this is?

  41. #41
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, sorry to trouble you again. As well as the problem i'm still encountering above. I've noticed that if i start my codes from sheet2 instead of sheet1 then they start from 0001 i.e PSCXXXX-S-0001?

  42. #42
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, x is just a string of 4 x's so will always be pscxxxx-
    Last edited by defenders; 01-14-2018 at 06:21 PM.

  43. #43
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Hi zbor, many thanks again for your time and persistence. This worked great, until i tried to alter the end date then i received a run time error. Debug showed this line in the macro:

    Please Login or Register  to view this content.

    Any ideas?
    Last edited by jeffreybrown; 01-17-2018 at 07:19 PM. Reason: Code tags please!

  44. #44
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    i'm not sure what you mean, all the PSCXXXX codes are set properly in column D. What data do you mean?

    I was also thinking of adding a time column next to the code column, using this macro

    Please Login or Register  to view this content.

    where xcellcolumn = 4 is column D and xTimeColumn = 13 is column M

    So when a drop down choice is selected from column D it will auto populate column M with the time

    can this be easily incorporated into the existing macro or run along side separately?
    Last edited by defenders; 01-19-2018 at 04:10 AM.

  45. #45
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    thanks.

    I'm still unable to figure out why changing the end date is causing an error.

    Also if column D value is changed in one sheet it affects names in N column in all the latter sheets as you mentioned, this is bit of a concern. Is it possible that if a value is changed in column D in any sheet that it won't affect/change names in N column in the latter sheets?

  46. #46
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    Quote Originally Posted by defenders View Post
    thanks.

    I'm still unable to figure out why changing the end date is causing an error.

    Also if column D value is changed in one sheet it affects names in N column in all the latter sheets as you mentioned, this is bit of a concern. Is it possible that if a value is changed in column D in any sheet that it won't affect/change names in N column in the latter sheets?
    any suggestions?

  47. #47
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: alphanumeric formula help

    It seems like if a drop down selection in column D is changed it only affects the values after in Column N and not in the previous sheets. Is it possible to prevent this from happening? As i don't the newer values to change. i.e if i change an L code at the beginning of a sheet to an S code then then later S codes after are changed. How can i stop this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array Formula for numeric and alphanumeric ID
    By turist in forum Excel General
    Replies: 3
    Last Post: 01-11-2016, 04:44 PM
  2. Replies: 2
    Last Post: 11-06-2014, 05:07 PM
  3. [SOLVED] length and alphanumeric formula (quick formula question)
    By nawGo in forum Excel General
    Replies: 4
    Last Post: 06-17-2014, 12:49 PM
  4. [SOLVED] Alphanumeric 'ISNUMBER' formula to return 'Yes' or 'No'
    By RuthieBuxton in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-08-2013, 10:27 AM
  5. [SOLVED] Alphanumeric autofill formula
    By frosty350 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2013, 04:43 PM
  6. Replies: 3
    Last Post: 04-23-2012, 10:18 AM
  7. Alphanumeric formula
    By Robert Christie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-19-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1