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 -
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.