Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel Formula help

    I have a spreadsheet that will be populated using the following formula:

    =IF(OR(A5=7900000,A5=7900002,A5=7900001),"C1002",I F(OR(A5="9572V",A5=7900018),"A1002",IF(OR(A5=79000 04,A5=7900009),"B1002","")))

    I need the C1002, A1002 and/or B1002 to increase by 1 (one) on every line.

    I would like it to look like this:

    7900000 C1002
    7900018 A1003
    7900004 B1004

    Any suggestions!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    In the next row you use something like this and copy it down if there are no blanks in the datarange:
    Code:
    =IF(OR(A6=7900000,A6=7900002,A6=7900001),"C"&VALUE(RIGHT(B5,4))+1,IF(OR(A6="9572V",A6=7900018),"A"&VALUE(RIGHT(B5,4))+1,IF(OR(A6=7900004,A6=7900009),"B"&VALUE(RIGHT(B5,4))+1,"")))
    If there are going to be blanks, I would recommend creating an intermediate column of the current value and then use the max of this +1.

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks - I actually need something slightly different. Here is my new formula -

    =IF(OR(A5=7900002,A5=7900003,A5="9572V",A5=7900018 ),"A001C",IF(OR(A5=7900006,A5=7900011,A5=9597),"A0 01P1",IF(OR(A5=7900009,A5=7900000,A5=7900017,A5=95 64,A5=7900001,A5=7900004),"A001","")))

    So, I need the A001C to move to n002n - can you still provide a remedy?

    Thanks again-

  4. #4
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    There will be no blanks!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    How about in row 6 and copied into the others:
    Code:
    =IF(OR(A6=7900002,A6=7900003,A6="9572V",A6=7900018),"A"&TEXT(MID(B5,2,3)+1,"000")&"C",IF(OR(A6=7900006,A6=7900011,A6=9597),"A"&TEXT(MID(B5,2,3)+1,"000")&"P1",IF(OR(A6=7900009,A6=7900000,A6=7900017,A6=9564,A6=7900001,A6=7900004),"A"&TEXT(MID(B5,2,3)+1,"000"),"")))
    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    chaipek (2014-07-17)

  7. #6
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It worked - THANK YOU, THANK YOU!!!
    Last edited by chaipek; 2014-07-17 at 13:19.

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You are very welcome.

    Steve

Posting Permissions

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