searching for a value over multiple tabs- formula not working

I have attached a sample spreadsheet. On the tab labeled "patent summary sheet", I am trying to search column A in all tabs for the value in column A of the patent summary sheet. When I find a match for that value, I want to return the value of the box 15 rows and 14 columns over from that found value. The formula that I built is

=OFFSET(INDIRECT(CELL("address", INDEX(INDIRECT("'" &'Sheet lists'!$B$2:$B$9&"'!" & "A1:A400"), MATCH(A2,INDIRECT("'" &'Sheet lists'!$B$2:$B$9&"'!" & "A1:A400"),0)))),15,14)

Its an array formula so I hit control-shift-enter to enter it.

"Sheet lists" is a named array that contains the names of the sheet tabs.

The problem I am having is that only values found on the first sheet (essentially the sheet returned by $B$2 on the $B$2:$B$9 array in the formula) do not give an error.

Its like the formula just thinks I have $B$2 instead of $b$2:$b$9.

If you look at column C of the patent summary sheet, only the values found on $B$2 return a value for this formula. the rest are #N/A

I have spent hours pouring over this formula and I cannot find my error.

Any help would be much appreciated.

C-L

Thank you, its a bit cumbersome because I have 54 tabs on the full sheet, but Ill hide those columns. That will do the trick!