Return Value Based on Match in Another Sheet using a Relative Sheet Name

I've been enjoying this message board greatly. So far I haven't had a need to ask a question because I've been able to use solutions to questions other people have asked. But today I hit a brick wall. I don't know if my search parameters are just off or what... I don't know how best to approach this, giving sufficient information for someone to actually help me without making people's eyes glaze over from boredom, so I apologize in advance for the meandering post.

I have a ridiculously large set of workbooks, each representing a project, and each containing hundreds of sheets which each represent structures. I have a master DATA workbook that contains a line item entry for every sheet in every workbook, hyperlinks to all the individual sheets, as well as line item information, summaries, calculated values by which each of the individual sheets are populated.

Every DETAIL sheet has a universally consistent block of 5 rows information at the top that contains various things like:

The relative location of the sheet in the workbook (always in cell A1), which is used to find row in the master DATA sheet:

=SHEETOFFSET(A1,-1,0)+1

Various data entered into the master DATA sheet which is used for calculations and references in the detail sheets:

=INDIRECT(ADDRESS($A$1,22,1,1,"Data"))

There are five summaries and breakouts of the data (cells C1 to G1) on each individual cost sheet so I can easily pick them up and feed them back the DATA sheet:

DETAIL Sheet Formula (D1):

=INDIRECT("G"&IF(COUNT(MATCH("*"&"Total Soft Costs"&"*",$A$3:$A$91,0)),MATCH("*"&"Total Soft Costs"&"*",$A$3:$A$91,0)+ROW($A$3)-1,"Not Found"))

Corresponding DATA Sheet Formula:

=INDIRECT("'P"&$A16&" "&$B16&$C16&"'!"&"D1")

This all works fine and dandy. But now I need to feed most of the detail values from all the DETAIL sheets back into the master DATA sheet. I've tried using a combination of INDIRECT / ADDRESS / MATCH formula variants based on the formula examples I listed above, but I simply cannot get it to work. Because of the variable layout of the datasheets outside of the A1:J10 universal data block, I cannot simply make a summary at the top of the sheet for each line item.

So my question is this: How do I look up the values en mass? In my DATA sheet I want to create a relative formula I can copy across and down all the fields and entries in the data set. Knowing the field (column header) in DATA may or may not be found somewhere in each DETAIL sheet?

I've tried doing a match to find the location of field in each DETAIL sheet so I can return an offset of that value, but MATCH function does not seem to work on ranges in other sheets:

=MATCH(AE13,"P"&$A16&" "& $B16 & $C16 &"!$B5:$B1000",0)

A few notes about the master DATA sheet:

Column A contains the project number
Column B contains the project acryonm
Column C contains the structure number
Column D contains the project workbook
Row 15 is the column headers
Row 16 is the start of the dataset (which goes on for thousands of rows and grows regularly)

So =D16 & "'P" & $A16 & " " & $B16 & $C16 translates into workbook and sheet name "[SC3.xlsm]'P1 AH0"

Any suggestions? I know how to mask the returned results if the value isn't found. I just don't know how to find the value when its not in a set location on each sheet.