Formula for copying over relevant data from one table to populate another table

Good Day Folks,

I am really between a rock and a hard place with this issue that I am having and was hoping that folks with far more knowledge than I can assist with. I am not bad in Excel but I am far from where I should be

So here is the issue:

SCENARIO – see attached Example with around 10,000 rows:
I have a worksheet with 172,000 + rows and about 30 columns - called the DATA tab. I then add new worksheets that looks at the DATA tab and pulls out certain information from this tab to then create various tables and graphs - basically the aim of all of this is to create Financial reports. I have done most of the exercise but am now stuck on the following:

ISSUE:
See attachment: "Example Data 1"
So - what is required is for a formula to look at the information in the DATA tab. It should look at
1. Column H (WO Type) to determine what the type is. If it is a "Quote" type it then needs to,
2. Look at Column Z (Capital?) to determine if there is a YES in this field.
3. It then needs to look at Column AL (Division). It needs to look for Client 1
4. If it is a QUOTE and it is also a YES and it is CLIENT 1 - then it must copy over all the blue highlighted columns to the tab which I called "Needs to populate this".
5. The difficulty is also that one of the columns to be copied over, namely Column AJ (Status), needs to populate different columns based on the status – probably easier explained if you look at the table that it needs to populate.
6. In addition, as this table is copied over to Word automatically, there can only be a maximum of 40 rows per table. Is this possible?

I hope that this is enough information and that it is easy to achieve.

Thank you in advance.

Take care,
Andrew