Auto-Populate
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
-
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
-
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!:)
-
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
-
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!
-
Brian,
Kudos to you sir. Thanks and much appreciated !
V_
-
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), "")
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 #)
-
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), "")
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Genevieve .... Thanks so much. True genius between both you and Brian.
Kind regards.
Vince
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!