Next asset tag value formula

I'm using Smartsheet as an inventory management system which is working very well. Is there a formula that I can use in the sheet summary to search the values in a column to find the max value? This should give me the last asset tag number used. I am using =MAX([ASSET TAG]:[ASSET TAG]) but it's giving me a 0. We do have a prefix before the number. Could that be causing the issue? For example 1-ABC-00045.

Thanks,

Mel

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 06/12/23

    Hi @mgilkessmith

    You can certainly do it with a helper column if the number section of the asset tags is the same. The formula for this would be (going from your example):

    =VALUE(RIGHT[ASSET TAG]@row,5)

    The VALUE is used to convert the result you'd get from just using the RIGHT formula into a usable number (e.g. 00001 becomes 1).

    You can then use the MAX formula in your summary on this helper column.

    Hope this helps, if you've any questions etc. then just ask!

  • @Nick Korna Thanks! So, the formula I used is =RIGHT([ASSET TAG]@row, 6) which worked in my helper column to just produce the numbers. However, in my sheet summary, I still get a 0. Is this because my number begins with a 0? Such as

    1-ABC-000005


    Thanks again!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Yes, all the leading 0s make it not act as a number for any formulas unfortunately - hence the VALUE part in my suggestion which does make it a number.

    If you had 2 helper columns (1 with the text number "00005" and the other with the value "5") you could use these to do a VLOOKUP or INDEX(MATCH) to get the result with the 0s (or even just the 1-ABC-00005) in your summary if that's preferable.

  • @Nick Korna thank you for the assistance. I am now having an issue where the formula is returning a 0. I think it's due to the blank cells in the column. How do you ignore blank cells in this formula?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    @mgilkessmith,

    You can alter the formula:

    =IFERROR(IF(VALUE(RIGHT([Completed Date]@row, 5)) = "0", "", VALUE(RIGHT([Completed Date]@row, 5))), "")

    This should stop any 0s cropping up (and also any invalid results).

    Let me know if it works ok for you! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!