Engineer Project Allocation App

[MonkeyBrunt]

Hi I am new here, and I am writing an Excel workbook to track the project allocation per engineer.
All is going well, except I have run into a problem...

I assign role/skill to each of my engineers (Network, Server, VMware etc)

I am trying to total the use per skill and demonstrate in a table as I can quite easily do per engineer.

How to I run a query to identify the engineers assigned to a skill (eg Network) and then run a query on the Engineer allocation table to populate a usage by skill table?

Unfortunately, I am unable to use any VBA in this workbook.. or it would be finished weeks ago!

TIA

[Gregb11]

It's probably best if you attached a sample workbook. Please follow the instructions iin the yellow banner at the top of this page.

[MonkeyBrunt]

Thanks, I have created a new post with an attachment as requested
#post5350429 - Not permitted to post URLs yet

[loginjmor]

Hello -

I pulled your spreadsheet from your duplicate post. Some of your engineers' skills have multiple categories. For example, on the Skills Tab, Storage Engineer 14 has both Backup and Storage categories. How do you want that handled at the bottom of your Staff Tab? Do I count Engineer 14's time twice? Both for Storage and for Backup?

[MonkeyBrunt]

Hello -

I pulled your spreadsheet from your duplicate post. Some of your engineers' skills have multiple categories. For example, on the Skills Tab, Storage Engineer 14 has both Backup and Storage categories. How do you want that handled at the bottom of your Staff Tab? Do I count Engineer 14's time twice? Both for Storage and for Backup?

Hi
Thanks for looking at this. The engineers in multiple categories is done deliberately. Each team is multi skilled and I need to demonstrate not only the engineer's availability but the skill's availability too.
This is a tool to demonstrate where we are short of both engineers or/and skills.

In answer to the question "Do I count Engineer 14's time twice? Both for Storage and for Backup?" - yes

[AliGW]

Please post the sample sheet to this thread.

[MonkeyBrunt]

Ad requested sammple file attached here

[loginjmor]

Hi -

OK - so I had to do a couple of things:

1. On the Skills Matrix tab, I had to add a table that pulls out a unique list of classifications by skill category (Network, Storage, Backup, etc.). I used the classic INDEX/MATCH/COUNTIF array formula that looks like:

=IFERROR(INDEX(tSkillsMatrix[Engineer],MATCH(0,IF(H$1=tSkillsMatrix[Category],COUNTIF(H$1:H1,tSkillsMatrix[Engineer]),""),0)),"")

Just google "Extract Unique List with multiple criteria" and you will get several web pages that have very detailed explanations of how this works. Anyway, this is highlighted in peach color on that spreadsheet.

Then, on the Staff Tab, I copied your table that was listing all of the staff classifications by category and used that structure to instead sum up the days on the Resource Tab according to the list on the Skills Matrix I created. The formula looks like:

=SUM(SUMIF(CResource[Engineer],'Skills Matrix'!$H$2:$H$9,Resource!H$5:H$12))

This formula resides in Cell F36, which is the Jun-20 month end reporting column. This is also an array formula. Since you already have array formulas in your spreadsheet, I'm assuming you know what those are. The interesting thing about this formula is since SUMIF only accepts a single criteria, I have to make it an array formula to accept multiple classifications. But what I learned is SUMIF, as an array formula, will still only return the first value in its array. So you have to enclose it in a SUM function to sum up the SUMIF array. So, I learned from this as well.

Attached is your revised spreadsheet with the above modifications.

Hope this helps.

[MonkeyBrunt]

Hi -

OK - so I had to do a couple of things:

1. On the Skills Matrix tab, I had to add a table that pulls out a unique list of classifications by skill category (Network, Storage, Backup, etc.). I used the classic INDEX/MATCH/COUNTIF array formula that looks like:

=IFERROR(INDEX(tSkillsMatrix[Engineer],MATCH(0,IF(H$1=tSkillsMatrix[Category],COUNTIF(H$1:H1,tSkillsMatrix[Engineer]),""),0)),"")

Just google "Extract Unique List with multiple criteria" and you will get several web pages that have very detailed explanations of how this works. Anyway, this is highlighted in peach color on that spreadsheet.

Then, on the Staff Tab, I copied your table that was listing all of the staff classifications by category and used that structure to instead sum up the days on the Resource Tab according to the list on the Skills Matrix I created. The formula looks like:

=SUM(SUMIF(CResource[Engineer],'Skills Matrix'!$H$2:$H$9,Resource!H$5:H$12))

This formula resides in Cell F36, which is the Jun-20 month end reporting column. This is also an array formula. Since you already have array formulas in your spreadsheet, I'm assuming you know what those are. The interesting thing about this formula is since SUMIF only accepts a single criteria, I have to make it an array formula to accept multiple classifications. But what I learned is SUMIF, as an array formula, will still only return the first value in its array. So you have to enclose it in a SUM function to sum up the SUMIF array. So, I learned from this as well.

Attached is your revised spreadsheet with the above modifications.

Hope this helps.

Thanks so much for this, I will check it out today