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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!