List of values based on criteria in two places...

Joshyd

New Member
Joined
Mar 6, 2019
Messages
17
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","")}
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi, it is extra long formula o_O

Please put data example using add-in specifically for this, you can find it can be found here XL2BB.
Pay attention to this post XL2BB 2 Squares
 

Joshyd

New Member
Joined
Mar 6, 2019
Messages
17
Here is example:


Cell Formulas
Range(s)Formula
O7:R12O7{=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(O$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$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(O$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$C$3:$C$"&Z))-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(O$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$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(O$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$C$3:$C$"&Z))-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(O$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$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(O$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$C$3:$C$"&Z))-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(O$2),IF(IFERROR(MONTH(INDIRECT("'Pending Start Date'!$T$3:$T$"&Y+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$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(O$2),IF(IFERROR(MONTH(INDIRECT("'Closed Roles'!$T$3:$T$"&Z+3)),0)=MONTH(O$2),ROW(INDIRECT("Jon!$C$3:$C$"&Z))-2)),ROW(1:1)),1)),""))," Days","")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top