Can you select a sheet reference by formula?
Situation: I have many sheets with bids from different vendors (all sheets are the same structure). I have a separate sheet where I am comparing those bids. I will select a winning vendor on each row. This is feeding other sheets to inform various departments with other information from the winning vendor bid sheet.
Need: I want to use INDEX COLLECT (or something similar) to pull the information into the department sheets without having to manually adjust each row to get the correct information. Somehow the INDEX COLLECT has to point to the correct vendor bid sheet.
Any suggestions would be appreciated.
Answers
-
Are you able to provide some screenshots of what you have as well as manually entered "mock" data to show what you are trying to accomplish via formula?
-
Paul
Here is a simplified example:
I have a Budget sheet,
3 different Vendor Bid Sheets. The Vendor Bid sheets follow the same format as the budget sheet and use the Index(Collect) in the green shaded columns:
And a Vendor Selector which pulls in the Total Price from each of the Vendor Bid sheets (see purple shading)
The issue is the Vendor Item Info column. I may have as many as 50 info columns related to an item so using the logic as was used for the cost is not feasible. What else can I use to pull that information in (note that not all rows for a single job may go to the same vendor (in this case, the Vendor Item Info needs to come from Vendor C Bid ("Longhorn") and not from Vendor B Bid ("Hereford Cattle").
I have set these up in a separate workspace if you need access.
Sincerely,
Rich
-
Hope you are fine, if you like me to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.
My Email for sharing : Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassim,
I have granted you access to the entire workspace
-
Bassim,
When might you have time to review the sheets in the workspace?
-
I hope you're well and safe!
Feel free to share it with me as well if it's urgent. Otherwise, you can wait for Bassam to get back.
(share too, andree@workbold.com)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andree,
I have shared the workspace with you. Thanks for the assistance.
Rich
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Do you have a resolution to this yet from @Andrée Starå, @Bassam Khalil, or @Paul Newcome?
From my understanding, you would need to write a nested IF statement with a different INDEX(COLLECT for each individual sheet. Then you would have the IF statement look at your "Winning Vendor" column and based on the value there, use the correct INDEX(COLLECT.
Ex:
=IF([Winning Vendor]@row = "Vendor A", INDEX(COLLECT({Value to bring back - Sheet A}, {Criteria Column 1 - Sheet A}, "Criteria 1")), IF([Winning Vendor]@row = "Vendor B", INDEX(COLLECT({Value to bring back - Sheet B}, {Criteria Column 1 - Sheet B}, "Criteria 1"))...
and so on.
Does that make sense? There isn't a range that can look across multiple sheets. Each cross sheet reference will be specific to its own sheet.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!