Formula that references a different sheet each row based on set criteria?

Question

I'm wondering if someone can help me. I have a Google sheets workbook with more than 50 individual sheets. This workbook has a primary 'index' page that collates set data fields from each sheet. I'm currently expanding this but the formula I am using requires me to change the referencing sheet per row so the data is pulled for the correct sheet. My questions is, is there a way to automate this process as it is incredibly time consuming?

As an example -

Column A is Product -

• T-shirts
• Shirts
• Jeans
• Trousers
• Shoes
• Boots
• Etc.

Each of these products in row A has a separate referencing sheet.

Column B - Damaged on delivery

Column C - Damage on transit

Column B & C are referencing data from each sheet and producing that data on the index page against the correct product.

I would like a formula that references each individual sheet based on the sheet name and the name on the index page.

Hope this is enough information. Any help will be greatly appreciated.

Answer 1

Have you considered using a combination of ADDRESS and INDIRECT?

ADDRESS has the advantage of being able to optionally include the sheet name. And since each item in Column A is the name of the respective sheet, this makes it very easy to build the address.

The formula in Cell B2: is: =indirect(address(7,2,4,true,A2))

---

SAMPLE- Index page

---

SAMPLE - T-Shirts page