Can I auto-populate a cell with available license numbers listed in another column, sheet or report

mccoy_FSI

We share license numbers and they are issued manually now. The process is, we look at the required dates, usually about three weeks, then look to see which license numbers are not in use during those dates and manually assign it. We would like to automatically assign it with a number not in use during the required dates.

Mike TV

@mccoy_FSI

You can try using an INDEX(COLLECT formula. At the end of the formula you'll put a 1 for the row index # to get the first result. You can copy/paste the formula into cells going down in a column across however many cells you need to collect license #'s for. Just change the number at the end from a 1 to a 2, then a 3, etc for each row. You can add an IFERROR to it as well so when it reaches the final one it can just make a blank cell instead of an error.

If you need further assistance, please provide an image of the sheet you're querying. Remember to hide any sensitive data.

mccoy_FSI

Mike, let me explain it more simply because I'm not sure your solution would work. I need to assign a number 1-10, but there cannot be a duplicate in the column and would like to do it automatically.

Mike TV

@mccoy_FSI

Could you share an image (without sensitive data) of your list of license #'s and I'll show you what I'm talking about?

mccoy_FSI

Mike TV

@mccoy_FSI

Where do the 3 weeks dates come into play?

mccoy_FSI

Forgot about that part. So overlapping dates couldn't share license numbers, but rows with the end date in the past could be ignored. It might be easer to filter the column to hide end date in the past rows, not sure.