Is there a function to find a Max or Min for text?


I know I can do this with a helper column, but I'm trying to avoid that since this sheet copies to a bunch of others and I don't want to have to go and hide the column in all of the downstream sheets.

I have a ID column that consists of alpha and numeric (ex. ENV-032) in sheet A. When someone fills out a form to add a new metric, they select a category that relates to the letters in the ID, and I need to find the highest associated number and add one to get the new metric ID. I have the new entry copying to a helper sheet that has some additional columns to do some other calculations, and moving to a helper sheet that pulls info from the first to fill out the columns needed. Once the columns are filled out the row moves back to the initial sheet and gets copied out to a bunch of sheets downstream.

I've tried the following:

  1. MAX(COLLECT([metric id], [category],category@row)) //MAX doesn't work with text
  2. INDEX(COLLECT([metric id], [category],category@row), COUNT([metric id], [category],category@row)) //I have some older metrics in the list that aren't in order and some have different text values so this isn't returning the ID with the highest number every time

Am I missing a function that can easily look at the alphabetical order of text in a range? If not, is there any other way to do this without adding a column to the sheet that has all of the ids?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!