Database Index: In Matrix, Given Column, Find Value, Return Index / Row

[jolyeu]

Until today mostly all of my excel related questions over the last 6 years (!) have been able to be solved by Mr. Excel so I've never actually bothered to register for an account. This one has me stumped however, so here I am! Hopefully it proves to be a challenge for someone .

I have a staffing database where each project has an index number, and beside each project I assign staffing by analyst initials (AVB, LHO, CLE).
I created the below table where each row heading represents a project number, and the data fields contain whether or not the
I am trying to develop a dynamic

 

[jolyeu]

well - we're off to a good start. just fat-fingered the send post too early...full text below

Until today mostly all of my excel related questions over the last 6 years (!) have been able to be solved by Mr. Excel so I've never actually bothered to register for an account. This one has me stumped however, so here I am! Hopefully it proves to be a challenge for someone .

I have a staffing database where each project has an index number, and beside each project I assign staffing by analyst initials (AVB, LHO, CLE, etc.):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Project[/TD]
[TD]Analyst 1[/TD]
[TD]Analyst 2[/TD]
[TD]Analyst 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Research[/TD]
[TD]AVB[/TD]
[TD]CLE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client Materials[/TD]
[TD]AVB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Weekly Comps File[/TD]
[TD]CLE[/TD]
[TD]DRA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Quarterly Comps File[/TD]
[TD]AVB[/TD]
[TD]KSD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Internal Committee[/TD]
[TD]LHO[/TD]
[TD]CLE[/TD]
[TD]KSD[/TD]
[/TR]
</tbody>[/TABLE]


I created the below table where each row represents a project number and each column represents an analyst (this is so it can be dynamic as staffing changes). The table returns whether the analyst is staffed on each project and the order for each analyst:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Index[/TD]
[TD]AVB[/TD]
[TD]LHO[/TD]
[TD]CLE[/TD]
[TD]KSD[/TD]
[TD]DRA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


All I need to do next is to develop a dynamic report that, given an analyst's initials, the report will return all of the projects that analyst is working on. For example, if I wanted to run "AVB," the output would look like:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]#[/TD]
[TD]AVB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Research[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client Materials[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Quarterly Comps File[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If "KSD", it would look like:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]#[/TD]
[TD]KSD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Quarterly Comps File[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Internal Committee[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

etc.

Last thing - I can't use VBA since it creates significant delays with our email server security which flags all macros.

Thanks in advance!