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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]11/1[/TD]
[TD]11/2[/TD]
[TD]11/3[/TD]
[TD]11/4[/TD]
[TD]11/5[/TD]
[TD]11/6[/TD]
[TD]11/7[/TD]
[TD]11/8[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD][/TD]
[TD]Available
[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD][/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Available[/TD]
[TD]Available[/TD]
[TD]Available[/TD]
[TD]Available[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD][/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[TD]Available[/TD]
[TD]Need Schedule[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD](Dynamic Date Based on Table Above)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD](dynamic) (Available or Need Schedule)[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD](Name) [/TD]
[/TR]
[TR]
[TD](Name)[/TD]
[/TR]
[TR]
[TD](Name)[/TD]
[/TR]
[TR]
[TD](Name)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Date[/td][td]

1-Nov​

[/td][td]

2-Nov​

[/td][td]

3-Nov​

[/td][td]

4-Nov​

[/td][td]

5-Nov​

[/td][td]

6-Nov​

[/td][td]

7-Nov​

[/td][td]

8-Nov​

[/td][/tr]


[tr][td]

2​

[/td][td]Name[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

3​

[/td][td]John Doe[/td][td][/td][td]Available[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Need Schedule[/td][/tr]


[tr][td]

4​

[/td][td]Jane Doe[/td][td][/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][/tr]


[tr][td]

5​

[/td][td]Mark[/td][td][/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Available[/td][td]Available[/td][td]Available[/td][td]Available[/td][td]Available[/td][/tr]


[tr][td]

6​

[/td][td]Bob[/td][td][/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Available[/td][/tr]


[tr][td]

7​

[/td][td]Rachel[/td][td][/td][td]Available[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][td]Available[/td][td]Need Schedule[/td][/tr]


[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td]date[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td]

3-Nov​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td]status[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td]available[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

16​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

17​

[/td][td]#List#[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

18​

[/td][td]John Doe[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

19​

[/td][td]Bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
Row	Column	Value
Mark	Available

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]11/2/2015[/TD]

</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.