Need the final formula! Please help.

[aleech13]

Hit a road block. I am trying to create a formula that will automatically list 'A' people that are coming available in a given month. I got the formula that will list all the 'A' people, but I hit a road block on the part where it will list them only if they are coming available in the month specified. Here is my example set:

Sheet 1

Name	Type	Date Available
Buddy	A	1/1/17
Gator	B	1/19/17
Aaron	C	1/15/17
Eric	C	4/5/17
Ted	C	5/5/17
Butch	A	1/8/17

<tbody>
</tbody>

I want the names to automatically populate in the below table:

Sheet 2

January	A	B	C
1	(name)	(name)	(name)
2	(name)
3
4
5

<tbody>
</tbody>

I have only been able to write an INDEX formula to be able to list all the 'A' people, but I dont know how to make it list them conditional upon the date they are available.

=INDEX(SHEET1!A2:A7,SMALL(IF(SHEET2!B1=SHEET1!B2:B7,ROW(SHEET1!B2:B7)-MIN(ROW(SHEET1!B2:B7))+1,""),ROW(A1)))

Can anyone please help? Its almost like i need to fit in somewhere =IF(AND(SHEET1!C2:C7>=(Cell with 1/1/17),SHEET1!C2:C7<=(Cell with 1/31/17)),???????, "")

Can anyone please help?

Thanks,

Aaron

 

[CyrusTheVirus]

Maybe this could work? Ctrl+Shift+Enter, not just Enter.

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF((Sheet1!$B$2:$B$7=B$1)*(MONTH(Sheet1!$C$2:$C$7)=(MONTH(1&$A$1))),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")

 

[aleech13]

Thank you for your response Cyrus. It appears that I am getting a #name error after hitting CTRL+SHIFT+ENTER. I looked through the formula to see if there was a text error, I didnt see one.

 

[CyrusTheVirus]

Hm, it works for me based on the below setup (copy/paste the below into excel)

Enter formula into Sheet 2 Cell B2.

Sheet 1:

Name	Type	Date Available
Buddy	A	1/1/2017
Gator	B	1/19/2017
Aaron	C	1/15/2017
Eric	C	4/5/2017
Ted	C	5/5/2017
Butch	A	1/8/2017

<tbody>
</tbody>

Sheet 2:

January	A	B	C
1	Buddy	Gator	Aaron
2	Butch
3
4
5

<tbody>
</tbody>

 

[aleech13]

Still getting the error. It may have something to do with my excel? I was working at the office when I had the question, but came home. My Excel is 2003 at home. I think the one at the office is newer? Would that be the issue?

 

[CyrusTheVirus]

Yes that could be it. I don't think Excel 2003 has the IFERROR function. So, try it again at work.

You could try this for 2003...

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF((Sheet1!$B$2:$B$7=B$1)*(MONTH(Sheet1!$C$2:$C$7)=(MONTH(1&$A$1))),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2)))),"",INDEX(Sheet1!$A$2:$A$7,SMALL(IF((Sheet1!$B$2:$B$7=B$1)*(MONTH(Sheet1!$C$2:$C$7)=(MONTH(1&$A$1))),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))))

 

[aleech13]

Yup, that did it. I will try the other formula at work tomorrow.

Thank you so much Cyrus! I spent hours looking for a way to do this. I greatly appreciate your help!

 

[CyrusTheVirus]

You're welcome.

 

[aleech13]

Cyrus,

Is there a way to make the formula also base the return result off not just the month in which they come available, but also the year? I have some guys coming available in 2018 and its messing up my results for 2017

-Thanks!!

 

[CyrusTheVirus]

could you post some examples of the issue and how you want it to look ?