Match Function to Return Required Digits

mrb231021

Hi Smartsheet Community! I am running into a problem and need some help with a Row ID formula using the match function.

I have a project plan that is generating a unique Row ID, using a combination of an Auto ID Column Type (Row ID), the Client & Project Code, and a match formula to return the actual position of the row in the sheet (Auto-Number). When only using the Auto ID column, if I need to add a new row, the Row ID is no longer in numerical order.

However, when I reference my new Auto-Number column in an Index(Collect formula in another sheet (Document Tracker), I'm getting an #INVALID VALUE error once my Auto-Number exceeds 99.

Any suggestions on how to get my Match Formula in the Auto-Number column to return 3 digits? Eg, K001-P001-001? Here is my current formula for my auto-number column:

=[Client and Project Code]@row + "-" + (MATCH([Row ID]@row , [Row ID]:[Row ID], 0))

AngB789

Try this: Use TEXT() to pad your MATCH() result with leading zeros.
=[Client and Project Code]@row + "-" + TEXT(MATCH([Row ID]@row , [Row ID]:[Row ID], 0), "000")

• MATCH() returns the row’s position as a number.
  Wrapping it in TEXT(..., "000") formats it to 001, 002, … 100, ….
  
  Fix the #INVALID VALUE in INDEX(COLLECT)

• That error typically appears because INDEX() expects a numeric row index, but if you feed it the formatted text (e.g., "001"), it treats it as text and fails. The safest solution is to split into two columns:

• Row Position (Number column) — purely numeric:

• =MATCH([Row ID]@row , [Row ID]:[Row ID], 0)
  Display ID (Text/Number column) — your friendly formatted ID:

• =[Client and Project Code]@row + "-" + TEXT([Row Position]@row , "000"

Then, in your other sheet, have INDEX(COLLECT(...)) use Row Position (the numeric one), not the formatted Display ID.