I am generating an Excel sheet (New Sheet) of 12 rows and 19 columns. The purpose of this sheet is to return 12 IDs of random employees per year, one per month.
'Jan 2003' will feed back a random employee from the pool of employees that started in January 2003, the data of which is in another column in another sheet (INQuery Output (2019.11.08 13.29.xlsx)). In New Sheet, each column represents a year, while each row represents a month. They are filled with an excel formula that returns the random ID that fulfills the criteria of being in a specific month:
2003 2004 2005 ... 2016 2017 2018
Jan
Feb
Mar
...
Oct
Nov
Dec
In New Sheet, Jan 2003 = $B$2. My formula is: =INDEX('[INQuery Output (2019.11.08 13.29).xlsx]Sheet1'!$A$2:$A$23, RANDBETWEEN(1,COUNTA('[INQuery Output (2019.11.08 13.29).xlsx]Sheet1'!$A$2:$A$23)),1) Within the range of where the cells contain dates between Jan 1 2003 and Jan 31 2003 is $A$2:$A$23. All the dates in INQuery Output are already sorted in order, and the dates are formatted as 01/01/2003.
How do I modify the COUNTA() part of my formula to return the cell range that contains dates in between two dates, to feed into INDEX()?
Aucun commentaire:
Enregistrer un commentaire