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.

  • Hi, welcome to WebApps. I can't explain why there has been no response to your question; but we'd certainly like to help.
    – Tedinoz
    Aug 9, 2022 at 23:58

1 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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.