Can I auto-populate a cell with available license numbers listed in another column, sheet or report
![mccoy_FSI](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
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.
-
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.
-
Could you share an image (without sensitive data) of your list of license #'s and I'll show you what I'm talking about?
-
-
Where do the 3 weeks dates come into play?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!