Hello! We are needing suggestions on how to better setup / populate estimated costs in our current workflow below. We are also open to rebuilding the two sheets and overall workflow as well.
Currently we have the workflow setup as follows -
Sheet 1 - Column 1 = "Request Set NAME", Column 2 = "Price"
*Note - there are currently 54 rows / line items of Request Sets to pull data from.
Sheet 2 - Column 1 = "Test Set" multi select cell to choose from the 54 line items on Sheet 1, Column 2 = "Estimated Cost (data table)" that needs to populate the price from the specific Request Sets selected in the multi select cell
Our problem - the formula to populate the "Estimated Cost" on Sheet 2 is too long to include all 54 options. We need a different formula or a new sheet build + new workflow to populate this information correctly.
Current formula is below -
=IF(CONTAINS("NAME1", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME1"), 1)) + IF(CONTAINS("NAME2", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME2"), 1)) + IF(CONTAINS("NAME3", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME3"), 1)) + IF(CONTAINS("NAME4", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME4"), 1)) + IF(CONTAINS("NAME5", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME5"), 1)) + IF(CONTAINS("NAME6", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME6"), 1)) + IF(CONTAINS("NAME7", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME7"), 1)) + IF(CONTAINS("NAME8", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME8"), 1)) + IF(CONTAINS("NAME9", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME9"), 1)) + IF(CONTAINS("NAME10", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME10"), 1)) + IF(CONTAINS("NAME11", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME11"), 1)) + IF(CONTAINS("NAME12", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME12"), 1)) + IF(CONTAINS("NAME13", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME13"), 1)) + IF(CONTAINS("NAME14", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME14"), 1)) + IF(CONTAINS("NAME15", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME15"), 1)) + IF(CONTAINS("NAME16", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME16"), 1)) + IF(CONTAINS("NAME17", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME17"), 1)) + IF(CONTAINS("NAME18", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME18"), 1)) + IF(CONTAINS("NAME19", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME19"), 1)) + IF(CONTAINS("NAME20", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME20"), 1)) + IF(CONTAINS("NAME21", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME21"), 1)) + IF(CONTAINS("NAME22", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME22"), 1)) + IF(CONTAINS("NAME23", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME23"), 1)) + IF(CONTAINS("NAME24", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME24"), 1)) + IF(CONTAINS("NAME25", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME25"), 1)) + IF(CONTAINS("NAME26", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME26"), 1)) + IF(CONTAINS("NAME27", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME27"), 1)) + IF(CONTAINS("NAME28", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME28"), 1)) + IF(CONTAINS("NAME29", [Test Set]@row ), INDEX(COLLECT({Price}, {Request Set}, "NAME29"), 1))
