Can you select a sheet reference by formula?

Rich Stowell

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.

Paul Newcome

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?

Rich Stowell

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

Bassam Khalil

@Rich Stowell 

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

Rich Stowell

Bassim,

I have granted you access to the entire workspace

Rich Stowell

Bassim,

When might you have time to review the sheets in the workspace?

Andrée Starå

Hi @Rich Stowell

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!

Rich Stowell

Andree,

I have shared the workspace with you. Thanks for the assistance.

Rich

Andrée Starå

@Rich Stowell

Happy to help!

I'll take a look and get back to you!

Genevieve P.

Hi @Rich Stowell

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