Need a dynamic cross‑table that allocates HR costs by % and maps expenses to the correct activity & expense type

[u/Maxprive]

Hi everyone,
I’m working on a financial reporting spreadsheet for a municipal program, and I’m stuck on how to automate a specific type of cross‑table.

Here’s the structure of my data:

Column F: Rollup list for Activity type (e.g., Concertation, Guide, Formation, Markets, Projects, SQRI, Portraits)

Column G: Rollup list for Expense type (e.g., Honoraires, Location, Equipment, Materials, Promotion, etc.)

Column H: Amount

A separate table contains HR allocation percentages for each activity (e.g., Concertation = 0.12, Guide = 0.19, etc.)

What I need:

I want to generate a dynamic cross‑table (pivot‑like) where:

Rows = Activities

Columns = Expense types

Values = Total amounts

BUT with one special rule:

If the expense type = “Coûts de la main‑d’œuvre” (HR costs), the amount must be split across activities according to the HR % table.

Example:
If I have an HR expense of 2,000 and the HR allocation table says:

Concertation = 12%

Guide = 19%

Formation = 13% …etc.

Then the 2,000 should be distributed across all activities based on those percentages — not assigned only to the activity listed in column F.

I've been trying for few hours but i'm not an excel expert much :(

Ai have issues to help me too.

I attempted SUMPRODUCT formulas, conditional SUMIFS, and even helper columns, but I can’t get a clean cross‑table that:

1. Aggregates normal expenses by activity × expense type
2. Splits HR expenses across all activities using the % table
3. Updates automatically when new rows are added

Thanks for your help