Returning name values from a dynamic columns in an array

[dcolella15]

Here is the Data set I am working with.

What I am looking to do is Create a dynamic column where I can change the date and them return the Names of the people that are available to work.

	Date	11/1	11/2	11/3	11/4	11/5	11/6	11/7	11/8
Name
John Doe		Available	Need Schedule	Available	Need Schedule	Need Schedule	Need Schedule	Need Schedule	Need Schedule
Jane Doe		Need Schedule	Need Schedule	Need Schedule	Available	Need Schedule	Need Schedule	Available	Need Schedule
Mark		Need Schedule	Available	Need Schedule	Available	Available	Available	Available	Available
Bob		Need Schedule	Need Schedule	Available	Need Schedule	Need Schedule	Available	Need Schedule	Available
Rachel		Available	Need Schedule	Need Schedule	Need Schedule	Available	Need Schedule	Available	Need Schedule

<tbody>
</tbody>

Date	(Dynamic Date Based on Table Above)

<tbody>
</tbody>

Status

(dynamic) (Available or Need Schedule)

<tbody>
</tbody>

(Name)

(Name)

(Name)

(Name)

<tbody>
</tbody>

Hope this makes sense...But I want to select the Date, then the status from a dynamic dropdown (which i know how to do), and then comb the dataset, and return for me the names of the people who are available to work on that date.

Thanks for any advice/help!
Dan

 

[sheetspread]

The reverse pivot table will do this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

it will have the filters already set up so you can pick the dates too

Excel 2010
	A	B	C
1	Row	Column	Value
19	Mark	11/2/2015	Available
Sheet17

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		Date	1-Nov​	2-Nov​	3-Nov​	4-Nov​	5-Nov​	6-Nov​	7-Nov​	8-Nov​
2​	Name
3​	John Doe		Available	Need Schedule	Available	Need Schedule	Need Schedule	Need Schedule	Need Schedule	Need Schedule
4​	Jane Doe		Need Schedule	Need Schedule	Need Schedule	Available	Need Schedule	Need Schedule	Available	Need Schedule
5​	Mark		Need Schedule	Available	Need Schedule	Available	Available	Available	Available	Available
6​	Bob		Need Schedule	Need Schedule	Available	Need Schedule	Need Schedule	Available	Need Schedule	Available
7​	Rachel		Available	Need Schedule	Need Schedule	Need Schedule	Available	Need Schedule	Available	Need Schedule
8​
9​
10​
11​
12​	date
13​	3-Nov​
14​	status
15​	available
16​	2​
17​	#List#
18​	John Doe
19​	Bob
20​

In A16 just enter:

=COUNTIFS(INDEX($C$3:$J$7,0,MATCH(A13,$C$1:$J$1,0)),A15)<strike></strike>

In A18 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$18:A18)<=$A$16,INDEX($A$3:$A$7,
  SMALL(IF(INDEX($C$3:$J$7,0,MATCH($A$13,$C$1:$J$1,0))=$A$15,
  ROW($A$3:$A$7)-ROW($A$3)+1),ROWS($A$18:A18))),"")<strike></strike>

 

[dcolella15]

The reverse pivot table will do this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

it will have the filters already set up so you can pick the dates too

Excel 2010

	A	B	C
1	Row	Column	Value
19	Mark	11/2/2015	Available

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Thanks for the suggestion Sheetspread. This was the first thin that I tried, but it looks like in the original sheet the table headers are part of a reference formula, so the users didn't want to use the table method.

Looks like the formulas from the next post solved the issue. Thank you so much for your time!

Dan

 

[dcolella15]

Dead on Aladin Akyurek! looks like this worked perfectly. I could think through this, and I knew the functions were there, i just couldn't figure out the language.

I appreciate your time and effort!

Dan

 

[Aladin Akyurek]

Dead on Aladin Akyurek! looks like this worked perfectly. I could think through this, and I knew the functions were there, i just couldn't figure out the language.

I appreciate your time and effort!

Dan

You are welcome.