Dynamic table in a different sheet from multiple tables in other sheet

This may take a bit, so hope your not in a rush

EDIT-
deleted..opened wrong workbook...sorry

So, the PO#'s are unique to each supplier?...I noticed they exist in each sheet, but it seems only that supplier uses that PO#

For excel looking at them (except as adding something to them ) they ARE the same...if I click on H2 in 'Source Table Catfish' or H2 in 'Source Table CrabCo', I get the same value, so something has to separate the PO's from each other, otherwise, your consolidation sheet is just going to be a mess...what I'm trying to get at is IF one company has PO/Ref PO1, will ANY othere compapany be using that same Identifier?

I believe this is going to take extensive modification to your workbook, all the array formulas I have tried break on the possibility of the same PO number being assigned to another company at the same time,there needs to be someway to separate them (hence the array formulas), you might be better off creating tables, then a 'Super table using the other sheets as the source....just throwing out Ideas here...

I don't think it is impossible, it's just going to take some time...I haven't given up on it yet, just trying to let you know it is not going to a '1 Minute solution'

Okay, I finally got some time to spend on this, and I think I've come up with a formula solution (at least for the sample)
I've added 2 attachments which are the same solution, the first works for Excel 2007+, the second for lower than 2007 (not sure how much earlier, but '97-2003 should be fine), BOTH recquire macros enabled
The first thing I did was add a helper column (column CC) to each of the 'Source Table..' sheets to Identufy which rows contained data, then a helper cell (CD2) to return the number of rows with data
Then, on the 'Result Data' sheet, I added 5 helper columns (columns CF-CJ)
Column CF returns ALL the sheet names in the workbook using the Named Formula 'Sheets', which refers to =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") (GET.WORKBOOK is a built in macro, for now at least ) (Note - I added some blank sheets to simulate a full workbook)
Column CG returns the sheet names of the 'Source Table...' sheets
Column CH returns the same as CG, but with no blanks betweeen
Column CI returns the company name
Column CJ returns how many rows have data from the 'Source Table...' sheet (I have added some dummy data into the tables of each sheet to show multiple matched PO's in several sheets)
Column D returns the company name for as many rows as the 'Source Table...' for that company has data
Columns E-CB returns the data for the company in column D

I used CF to create a Line to define the bottom of the table for easy reference

The Difference between the two attachments:
In first Attachment (2007 +), I did not use any VBA
In second Attachment (lower than 2007), I replaced the IFERROR() functions with this VBA UDF:

Function IfErrorC(Arg1, Arg2)
    If IsError(Arg1) Then
        IfErrorC = Arg2
    Else
        IfErrorC = Arg1
    End If
End Function

Hope this helps

EDIT -
(***NOTE***) These formulas rely on the way the sample is setup, to make it more adaptable would require far more complex formulas