Combine data from two worksheets

I have been asked to automate a process in Excel which has been handled manually for years.
I have not had much experience with Excel beyond the basic functions so am not quite sure how this would be possible.
There are two spreadsheets involved and data must be applied from one to the other as follows.

The first spreadsheet is one where “Category Z” has its value split among Categories A,B,C,D,E and F. These values are assigned to users.

However, this value is only calculated in this spreadsheet, it is not actually applied.
This value now has to be applied on the second spreadsheet.

The second spreadsheet already has values applied to all categories including Z.
The requirement is that any value allocated to Z in this spreadsheet must be deleted and replaced by the allocated time determined in the first spreadsheet.
If there is already a value assigned to one of the A-F categories, this value must be increased by the amount decided in the first spreadsheet.
If there is no value assigned to A-F on the second spreadsheet, but now will have a value assigned from Z, a new column must be created to register this value.

An example of what I am trying to automate is attached.
Searching online, the best method I’ve found so far is SUMIFS() but that still leaves a lot of manual entries needed.
Does anyone know if this would be possible to automate with Excel?

The reason for Z being gone from the third spreadsheet is because Z is an undesired category in the final report
The main objective of this process is to remove any quantity that has been entered in Z and instead allocated to the other categories.
There might be some flexibility on how the final spreadsheet is layed out as long as that objective is met.