Formula for picking every other cell?

  • Example:

    Say I want the following cells to be captured up to 999 but every other one.

    B2,B4,B6 ect to G2, G3, G4 and

    B3,B5,B7 ect to H2,H3,H4 and so on.


    Is there a formula that can do this? I use the following formulas to generate serial numbers as seen in the attached. So far I have manually set the above mentioned locations but was hoping to do this with a formula instead similar to how my sequence formula works.

    Code
    1. =L11&TEXT(SEQUENCE(P2),"")
    2. =L2&M2&N2&TEXT(SEQUENCE(P2)+O2-1,"000")
    3. =L8&TEXT(SEQUENCE(P2),"")

    Example test.xlsx

  • Hello,


    You can use following formula to return even and uneven numbers :

    Quote


    =MOD(ROW(),2)

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • For even rows with auto sequence.

    Code
    1. =INDEX(B2:B12,SEQUENCE(ROUNDUP(P2/2,0),,,2))

    For odd rows with auto sequence,

    Code
    1. =INDEX(B2:B12,SEQUENCE(P2/2,,2,2))

    I received help on this in another thread I had elsewhere. Although the =mod works, and rather well, it wasn't exactly what I was looking for. These two were the ones that worked best given the nature of my Excel workbook. I'm posting these here in case someone else needs something similar. I'll also add a sample file at the bottom so you can see what I was trying to accomplish.

    Example test.xlsx

  • Thanks for sharing. In future please post links to any other Forums the question is asked in when opening the question.


    Note, that using ROW() in a serial number is not a good idea because the number will be fixed to that row. For example, you cannot sort data alongside it .

  • Thanks for sharing. In future please post links to any other Forums the question is asked in when opening the question.


    Note, that using ROW() in a serial number is not a good idea because the number will be fixed to that row. For example, you cannot sort data alongside it .

    The ROW part worked somewhat but wasn't exactly what I needed in this instance due to the nature of my workbook. So you are correct.

    I started this project to be a database for a label printer/finisher. We use NiceLabel software which needed me to keep the Excel sheet in a certain format to work. When I started I was entering all information manually for the S/N's as well as other information that was being printed.

    Also my apologies for not linking the other forum I had this question in (That thread was started to help with my auto generating part and just took off). I was unaware it was OK to do so and didn't out of respect for both forums. I'll do that now. In this thread on the board you can see the progress of my workbook. With yours and their collective help I have managed to really bring this serial number generator to a whole new level that I never thought could be possible. Furthermore, I truly appreciate you all. I never could have done this without the collective knowledge you all have bestowed upon me. Adding link to my other thread at the bottom.

    x-amount-of-serial-numbers-based-on-criteria