Need help with a formula
I am trying to get a formula for my Speaker Position column that will rank each speaker if there are multiple on a given date. I have tried using the AI generate formula and it keeps coming back with an error. Please help!
Answers
-
So you have three people all speaking at 10:10. So each of these people is number 1? And what happens on the second day? They are all number 2? Or do they reset to number 1 again?
You should fil out that Speaker Position with your desired result, and explain the logic of how that result is determined, and then we can help create the formula.
-
There is only 1 speaker a day. Those signed up on the same day are the backups in case the first person can not make it. So in the example above, in the Speaker Position column, I would need it to return the value of 1 on row 113, 2 on row 114, and 3 on row 115. I tried the AI formula generator inside Smartsheet and it kept giving me an error.
-
@mrseibt , there doesn't appear to be any information within the row for the formula to know who is 1,2 or 3. Adding a column and naming them "Primary", "Backup 1", and "Backup 2" would allow for a formula to set their precedence.
-
@jpaul the names get onto the sheet by people filling out a form. If I created a new column and used the Created Date, would it be able to make a formula based on this? They might all submit a response on the same day, but times will most likely be different. Could that work?
-
Add a helper column title "row" that just has the row numbers.
Here is a thread where various methods were described:
once you've got that, make your formula something that counts all instances of your speaker and time on that row and earlier rows, like this "=countifs([speaker and time]:[speaker and time],[speaker and time]@row,row:row,< = row@row)"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!