Filter data based on value and condition

So I've built something for you which isn't the greatest but should help cover what you need.

Raw Sheet
There are a few extra columns to the right of your raw data.
When/if you put data in here make sure these columns have the formulas copied down far enough to cover the information you have popped in.
Keep a backup in case you delete the formulas in error and then you can easily replace.

Helper Sheet
The first table looks the same as the one on the Dashboard. This accumulates the number of jobs/tasks for each type.
Underneath is a table which then breaks down your entered allocation figures.

The two tables to the right are used to determine what order the final table output will be shown in to keep things tidy there.

Dashboard
Have added to your allocation table some totals so you can see what is left to allocate and where you have allocated too many.
Note the numbers allocated don't match your original now so you will want to review this.

The final table is shown underneath listing jobs given as per your allocation. When you change the allocation table at the top this table will not update automatically as requested.
However if you right click on the table and choose refresh it will update. Best to allocate numbers first in one go then refresh save you doing it constantly.

The table itself has been created using Power Query. A quick search online and you will find basic guides to get you started on the basics.
In a nutshell the Raw sheet is loaded into PQ and then some actions are performed on the data to filter it down (which is where the extra columns come in to play). The final output is then sent to screen, i.e. the table on the Dashboard sheet.


Any questions let me know.

IN B11 then copy down

Please Login or Register  to view this content.

In C11 then copy across

Please Login or Register  to view this content.

For some columns Format is to copied from Raw sheet from respective columns.
Eg;Column F in Dashboard

OMGGGGG! you are a genious!

I need to understand this formula, I will study this.

Thank you very much! <3

Knew there would be a simple elegant solution. Just goes to show theres plenty to learn still!

Thanks for feed back and reputation.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.