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

Perhaps try, in D3:

=INDEX(Sheet1!$B$3:$Q$85,MATCH($C$2,Sheet1!$B$3:$B$85,0)+ROWS($A$1:$A1),COLUMNS($A$1:B$1))

copied down and over the whole matrix.

To hide the zeroes where there should be blanks, Format all the cells as: 0;-0;;@

Try this in D2, copy down then accross the matrix

=INDEX(OFFSET(Sheet1!$B$3,MATCH(Sheet2!$C$3,Sheet1!$B$3:$B$85,0)-1,1,26,15),MATCH($C$2,Sheet1!$B$3:$B$85,0)+ROWS($C$3:$C3)-1,MATCH(D$2,Sheet1!$C$3:$Q$3,0))

You can then introduce the Indirect() function and replace the sheetname in the formula with an indirect reference to the tab.

e.g

=INDEX(Indirect("'"&$A$1&"'!$B:$Q"),MATCH($C$2,Indirect("'"&$A$1&"'!$B:$B"),0)+ROWS($A$1:$A1),COLUMNS($A$1:B$1))

Where A1 would contain the lookup sheet name.

Who are your referring to?

Try in D3:

Please Login or Register  to view this content.

copied across the matrix

huh?