Auto-Populate

VinceA
VinceA ✭✭✭
edited 03/01/24 in Formulas and Functions

Hey Folks,

Is there a way to auto-populate a field in a column when an item # is entered on a separate column.

I have maybe 100 or so different Item #'s and depending upon what packaging line that item runs on, that item # would have a certain speed setting for that packaging line that would need to be displayed on a separate column when the item $ gets entered.

As reference, I have a separate sheet correlating which Item # gets what speed so maybe it could serve as some type of feeder/reference sheet to pull the pertinent data from onto the Line Schedule Sheet but just don't know how this would get facilitated.

example below and thanks.

Vince



Best Answer

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    In this case, I would recommend the following formula structure:

    =IFERROR(INDEX(COLLECT({Cross Sheet Reference - Speed}, {Cross Sheet Reference - Item #}, [Item #]@row), 1), "")

    Thanks!

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭

    It sounds like you might able to use an INDEX(COLLECT formula using your reference sheet.

    =INDEX(COLLECT({Cross Sheet Reference - Speed}, {Cross Sheet Reference - Item #}, [Item #]@row), 1)

    The cross-sheet references are tied to the Speed and Item # columns on your reference sheet. The formula should look for a match between that row's Item # and one on your reference sheet. Then, it pulls the speed from the first matching row on the reference sheet.

    Hope this helps!:)

  • VinceA
    VinceA ✭✭✭

    Brian,

    thanks a bunch. That looks to be what I was aiming for.

    What might be the proper =IFERROR(IF ...... = 0, "", formula string for keeping the field empty when on the Reference Sheet there isn't an ITEM # found so as to not create the #INVALID VALUE.

    Thanks

    Vince



  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    In this case, I would recommend the following formula structure:

    =IFERROR(INDEX(COLLECT({Cross Sheet Reference - Speed}, {Cross Sheet Reference - Item #}, [Item #]@row), 1), "")

    Thanks!

  • VinceA
    VinceA ✭✭✭

    Brian,

    Kudos to you sir. Thanks and much appreciated !

    V_

  • VinceA
    VinceA ✭✭✭
    edited 03/04/24

    Brian,

    may I trouble you one last time on this subject, or anyone else with expertise for that matter, as to how to adjust this formula to consider that there may be the same item # but under 2 different machine #'s which would yield 2 different BPM settings. I thought I had the formula correct but it isn't valid from my experimenting with it. This is what I thought might work as I just added {BXR/BLST MACH #} to what you suggested:

    =IFERROR(INDEX(COLLECT({BXR/BLST (BPM)}, {BXR/BLST MACH  #}, {BXR/BLST Item #}, [Item #]@row), 1), "") 


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @VinceA

    You would need to know what MACH # to look for within that range, to filter it:

    =IFERROR(INDEX(COLLECT({BXR/BLST (BPM)}, {BXR/BLST MACH #}, [MACH #]@row, {BXR/BLST Item #}, [Item #]@row), 1), "") 

    If you don't know what those numbers will be, you could use JOIN collect instead, to bring all matches into one cell:

    =IFERROR(JOIN(COLLECT({BXR/BLST (BPM)}, {BXR/BLST Item #}, [Item #]@row), " / "), "") 

    Cheers,

    Genevieve

  • VinceA
    VinceA ✭✭✭

    Thanks Genevieve,

    I don't believe what the formula you proposed is what my intent is. On my Feeder sheet, I have 2 of the same Item #'s on different packaging lines that call for different speeds, so when I put that Item # in my Main sheet correlating it to a specific line, I need it to reference the Feeder sheet as such as per below, so that I only see the correct speed setting (BPM) for that Item for that line # (Boxer #)


  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @VinceA

    Thank you for the screen captures! Since you have the Boxer # listed in your sheet you'll want to use that as your criteria:

    =IFERROR(INDEX(COLLECT({BXR/BLST (BPM)}, {BXR/BLST MACH #}[Boxer #]@row, {BXR/BLST Item #}, [Item #]@row), 1), "") 

  • VinceA
    VinceA ✭✭✭

    Genevieve .... Thanks so much. True genius between both you and Brian.

    Kind regards.

    Vince

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!