The Question
How can I create a Calculated column with a formula that works the same way as an Excel MAX formula?

I have a need to create unique ID's for process documents, but can't rely on the inbuilt ID field becuase if you delete a record SharePoint still reserves the ID for it. So there are no gaps in the set of Process Numbers I need to manually create this number.

Obviously I can do this by restricting entry of the number field to unique numbers but this wouldn't stop someone from entering 50 when there are only 30 records.

So ideally I was hoping to set the ProcessNumber field's default value as the result of =MAX(ProcessNumber)+1, but I have no idea how to.

1 Answer 1


Calculated columns don't work "vertically" in the library, only "horizontally" that is they cannot be used to determine something about any other items, only the current item. You could set a process number via workflow, event receiver, or possibly use the document id feature which gives each document a unique id apart from the list item id.

  • Thanks @PirateEric, just figured that out muself too, should have know that :( I think the Document ID would have same issue as the list ID. How would you recommend doing it by workflow? (I only have Designer)
    – CLockeWork
    Aug 6, 2013 at 15:35
  • You have to store the current max in a list, then on item creation, you have to look that up, add 1 to it, update the current item with that then update the list as well. You also have to introduce a "soft delete" option so users can flag the item for deletion, the lookup your current max, subtract 1 and update that value. Aug 6, 2013 at 16:09
  • Thanks @PirateEric, and sorry to keep asking questions, but how would I get the current max into another list if I can't get the max in general?
    – CLockeWork
    Aug 7, 2013 at 13:49
  • You have to seed the list with a value, so if its a fresh library, you set the list value to zero, then start calculating off it. Aug 7, 2013 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.