Hi there,
I am currently using a tracking spreadsheet to list out titles of people who started in my organization and the days it took to hire them. The problem I am running into is based on the fact they are occasionally stored in an interim place if they have accepted an offer, but not started yet. This is how my formula is set up right now:
{=IFERROR(IFERROR(INDEX("Place 1 titles",SMALL(IF(YEAR("Place 1 acceptance date")=YEAR("Date in question",IF(MONTH("Place 1 acceptance date")=MONTH("Date in question"),ROW("Range 1:last point of data"),FALSE),FALSE))),ROW(1:1)),1),),""),INDEX("Place 2 titles",SMALL(IF(YEAR("Place 2 acceptance date")=YEAR("Date in question",IF(MONTH("Place 2 acceptance date")=MONTH("Date in question"),ROW("Range 2:last point of data"),FALSE),FALSE))),ROW(1:1)),1),),"")}
the formula will also add a "; " if data is returned, then search for the days to hire in a similar structure, then add " Days" if days to hire is found.
The problem:
If there is a point of data found in "Place 1", then, when it is spread down to the next cell, it will return the 2nd smallest data point (this part of the formula: SMALL(IF("x",ROW(2:2)). This is a problem because when it is searching the second place, it is already looking for the second smallest point, meaning that it skips over the first. The more data in the first place, the more data that is skipped in the second place.
Solution I tried: The best thing that I came up with is to subtract a certain amount of rows based on the previous number of values (ROW(1:1)-COUNTA($A$1:A1), for example), but the problem with this is that it will always return the first value. So then you would miss out on all the other points. -- Perhaps there is a way to inspect if the first place is an error, and if it is, to restart at 1 or if there is a way to inspect if the above came from the first place, and if it did to subtract from the ROW(1:1)? I am not sure how to set this up...
The only thing I can think at this point is to create a hidden sheet that uses two columns to collect all the data from the two places, then use a third column to compile the data into one place for the formula to search from.
Here is the actual formula that I am using, but if you were to do some testing, you would need to make adjustments, that is why I spelled it out above. (I know it is a mess, but I am planning to add named ranges once I get it working)
{=IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$C$3:$C$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$C$3:$C$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$C:$E$"&Z+3))-2)),ROW(1:1)),1)),"")&IF(IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$C$3:$C$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$C$3:$C$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),"")=""," ","; ")&IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$W$3:$W$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$W$3:$W$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),"")&IF(ISNUMBER(IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$W$3:$W$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$W$3:$W$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),""))," Days","")}
I am currently using a tracking spreadsheet to list out titles of people who started in my organization and the days it took to hire them. The problem I am running into is based on the fact they are occasionally stored in an interim place if they have accepted an offer, but not started yet. This is how my formula is set up right now:
{=IFERROR(IFERROR(INDEX("Place 1 titles",SMALL(IF(YEAR("Place 1 acceptance date")=YEAR("Date in question",IF(MONTH("Place 1 acceptance date")=MONTH("Date in question"),ROW("Range 1:last point of data"),FALSE),FALSE))),ROW(1:1)),1),),""),INDEX("Place 2 titles",SMALL(IF(YEAR("Place 2 acceptance date")=YEAR("Date in question",IF(MONTH("Place 2 acceptance date")=MONTH("Date in question"),ROW("Range 2:last point of data"),FALSE),FALSE))),ROW(1:1)),1),),"")}
the formula will also add a "; " if data is returned, then search for the days to hire in a similar structure, then add " Days" if days to hire is found.
The problem:
If there is a point of data found in "Place 1", then, when it is spread down to the next cell, it will return the 2nd smallest data point (this part of the formula: SMALL(IF("x",ROW(2:2)). This is a problem because when it is searching the second place, it is already looking for the second smallest point, meaning that it skips over the first. The more data in the first place, the more data that is skipped in the second place.
Solution I tried: The best thing that I came up with is to subtract a certain amount of rows based on the previous number of values (ROW(1:1)-COUNTA($A$1:A1), for example), but the problem with this is that it will always return the first value. So then you would miss out on all the other points. -- Perhaps there is a way to inspect if the first place is an error, and if it is, to restart at 1 or if there is a way to inspect if the above came from the first place, and if it did to subtract from the ROW(1:1)? I am not sure how to set this up...
The only thing I can think at this point is to create a hidden sheet that uses two columns to collect all the data from the two places, then use a third column to compile the data into one place for the formula to search from.
Here is the actual formula that I am using, but if you were to do some testing, you would need to make adjustments, that is why I spelled it out above. (I know it is a mess, but I am planning to add named ranges once I get it working)
{=IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$C$3:$C$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$C$3:$C$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$C:$E$"&Z+3))-2)),ROW(1:1)),1)),"")&IF(IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$C$3:$C$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$C$3:$C$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),"")=""," ","; ")&IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$W$3:$W$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$W$3:$W$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),"")&IF(ISNUMBER(IFERROR(IFERROR(INDEX(INDIRECT("'Pending Start Date'!$W$3:$W$"&Y+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Y+3))-2)),ROW(1:1)),1),INDEX(INDIRECT("'Closed Roles'!$W$3:$W$"&Z+3),SMALL(IF(IFERROR(YEAR(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=YEAR(C$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(C$2),ROW(INDIRECT("Sam!$C$3:$C$"&Z+3))-2)),ROW(1:1)),1)),""))," Days","")}
