Offset not working

Hi

I am trying to get an offset formula to work. I have attached the following spreadsheet. the reason the formula is referencing cells in yellow is because it may change.

The spread needs to work so that it picks up different data from different tables which on tab 1. So hopefully it picks up the entire range of the table.

Hopefully I have made it clear, if not I apologise