Unable to pull information from all columns based on what is in another worksheet

Hi There! I am in need of assistance with this job costing template that pulls information from certain columns on each worksheet (Quoted; Awarded & On Hold; In Progress; Completed) to update the Active Projects & All Projects worksheets. Each week, I pull the Job Profitability Summary Report from QuickBooks and paste that information into each worksheet according to the Job S

The formulas I have used do not pull the information unless it's all in the same column. For instance, Active Projects needs to find the job number/name (008022 Test Job) from In Progress worksheet which could be in any one of the columns B-E. This is the formula I have been using:

=SUMIF('In Progress'!E:E,'Active Projects'!C4,'In Progress'!F:F)+SUMIF('In Progress'!C:C,'Active Projects'!C4,'In Progress'!F:F)+SUMIF('In Progress'!D:D,'Active Projects'!C4,'In Progress'!F:F)+SUMIF('Awarded & On Hold'!D:D,'Active Projects'!C4,'Awarded & On Hold'!E:E)

Currently, this formula updates the Job Status (column A on Active Projects) based on the worksheet that it finds it on (In progress or Awarded & On Hold) and should update the Act. Cost (column G); Act Rev (column I); Margin % (column O), on the Active Projects worksheet but only pulls the correct information if it's all in the same column, which is a manual process because I have to move the jobs over to column E.

I would like to pull the same information onto the All Projects worksheet but I can't get the Active Projects worksheet to work. Please see the sample that I attached.

Any assistance would be greatly appreciated!

Sorry, I must have attached the wrong one as there should have been 4. Please see attached.
These for should be pulling all the information from the In Progress worksheet
008022 Test Job
R00426 Test Job
J00160 Test Job

This one should be pulling from the Awarded & On Hold worksheet:
008001 Test Job

You will note 008022 does not pull over any information.

I wanted to thank you for reviewing that for me! I feel silly that is was just a space but at least everything works now.