dimanche 10 novembre 2019

EXCEL: Return cell address range for inserting into INDEX(RANDBETWEEN()) formula

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