Auto-Populate

VinceA

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

brianschmidt

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!

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

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

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

Brian,

Kudos to you sir. Thanks and much appreciated !

V_

VinceA

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.

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

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.

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

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

Kind regards.

Vince