Nested if & countifs to generate a prefix based on number of occurrences
Answers
-
You have it set as View Only so I cannot access the formula.
-
Sorry Paul - can you tell I'm new at this! https://app.smartsheet.com/b/publish?EQBCT=b42a98bd5885437b8a967cd0a12dfed7
-
This looks like the issue part:
COUNTIF(($[Supervisor forename]$1:[Supervisor forename]@row) + ([Supervisor forename]$1:[Supervisor forename]@row), ([Supervisor forename]@row + [Supervisor surname]@row))
What exactly are you trying to accomplish with that?
-
I'm trying to work out whether in the supervisor forename and surname rows (which is the first 4 characters of the identifier e.g. JADO) there is a repeat of the same e.g. "JADO" and if so, to place a lowercase b, c, d,..., etc. to signify it is the second, third, fourth etc. project of the same supervisor. Over years, there would be many projects for the same supervisor and so it needs to be clear how many they have supervised.
-
I think I see what's going on. Check out the sheet now. You are going to need an auto-number column with no special formatting, and I dropped a new formula into another column.
-
Okay thanks, what do I put into the auto-number column?
-
The auto-number column will self-populate when you save the sheet.
-
Okay. Have done that but its not generated the prefix in the identifier formula. I also noticed that the date is now wrong - its reading the day and year, rather than month and year. I'm based in the UK and so have my date column set to dd/mm/yy but maybe you have mm/dd/yy?
-
The formula I put in does not reference the day at all. It is
sup forename / sup surname / stud forename / stud surname / counter based on number of times sup is in that year / . / year
The names are grabbing first two characters and year is grabbing last two character.
-
okay, the original also had month too but I can that back in. The prefix still isn't showing though, if you can help on that?
-
I thought the portion where you have the month was the counter. That's where I placed the counter in my formula. I have adjusted it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!