Returning value based on drop down selection

[Mr Philip]

Hello Everyone,

My name is Anish. I'm creating a spreadsheet that performs different functions in other tabs based on a master spreasheet per unit. One specific thing I'm looking to do on a seperate tab is report on certain topics (LOA, AWOL, Orientation) so that it returns the value of the name of the individuals that are a "Y" in any of those 3 columns.

The way I picture it laid out in the reporting tab is this:

Dropdown 1 for report type:

ALL
LOA
AWOL
Orientation

A conditional TYPE dropdown that appears only if LOA is chosen in dropdown 1

Disability
Family
Maternity
Medical
Work Comp

Dropdown 3 for Unit:

ALL
A5
D2N
D2S
D4N
D4S
D6N
D7N
D7S

So each unit has their own tab with the raw data. It's laid out like this:

LAST</SPAN>

FIRST</SPAN>

TITLE</SPAN>

UNIT</SPAN>

TOUR</SPAN>

FTE</SPAN>

TKID</SPAN>

LOA</SPAN>

TYPE</SPAN>

RETURN</SPAN>

AWOL</SPAN>

RETURN</SPAN>

ORIENTATION</SPAN>

COMPLETION</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=6><COL><COL><COL><COL><COL><COL></COLGROUP>

So in the LOA, AWOL, ORIENTATION columns, the person managing the staffing for each unit would put Y if they were one of those things. There's a drop down for Type with the list mentioned above in dropdown 2. Both RETURN columns and the COMPLETION column are date columns.

I have already used the INDIRECT formula to have the TYPE dropdown on the reporting tab display the list in drop down 2 if LOA is chosen for drop down 1.

Essentially, this is what I'm hoping for:


Let's say we have the below in the raw data sheets for unit D7S:

LAST</SPAN>

FIRST</SPAN>

TITLE</SPAN>

UNIT</SPAN>

TOUR</SPAN>

FTE</SPAN>

TKID</SPAN>

LOA</SPAN>

TYPE</SPAN>

RETURN</SPAN>

AWOL</SPAN>

RETURN</SPAN>

ORIENTATION</SPAN>

Langainge</SPAN>

Donna</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Y</SPAN>

DISABILITY

3/15/15</SPAN>

Sesay</SPAN>

Zainab</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Baird</SPAN>

Lennox</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Smith</SPAN>

Constancia</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Glaze-Bernard</SPAN>

Sheryl</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Sonko</SPAN>

Rouguiatou</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Harris-Casey</SPAN>

Beverley</SPAN>

RN</SPAN>

D7S</SPAN>

2</SPAN>

Y</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=7><COL><COL><COL><COL></COLGROUP>

If I choose LOA in the REPORT TYPE dropdown, and select ALL in the TYPE dropdown, and choose unit D7S in the UNIT dropdown, I will get in my first alloted row: LAST</SPAN> FIRST</SPAN> TITLE</SPAN> UNIT</SPAN> TYPE</SPAN> RETURN</SPAN> Langainge</SPAN> Donna</SPAN> RN</SPAN> D7S</SPAN> DISABILITY</SPAN> 3/15/2015</SPAN> <TBODY> </TBODY><COLGROUP><COL span=6></COLGROUP> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> <TBODY> </TBODY> If they choose Orientation, there will be no type, and they could choose the unit or all. I can upload a spreasheet if needed. I've referenced a lot of the lessons on this site in creating other sheets and it has been a great help. I went from a novice to pretty good. I just took a test from a staffing agency based on overall knowledge, from beginner to power user and scored a 97 which I can attribute to this site. I just can't figure out the Excel logic on this issue. </SPAN> </SPAN> </SPAN> </SPAN> <TBODY> </TBODY><COLGROUP><COL><COL><COL span=6><COL><COL><COL><COL><COL><COL></COLGROUP> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> <TBODY> </TBODY><COLGROUP><COL><COL><COL span=6><COL><COL><COL><COL><COL><COL></COLGROUP> </SPAN>

</SPAN>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=6><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Mr Philip]

OK taking a step back it might be easier for my need to do a pivot table across all the specific worksheets with the data. if anyone does know a way to do the above, it would be def more aesthetically pleasing but I think I could get the job done this way.