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

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.

Answers

  • Mike TV
    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
    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
    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?

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @mccoy_FSI

    Where do the 3 weeks dates come into play?

  • mccoy_FSI
    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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!