Using the ID column to generate a number in a calculated column.

[SuzanoSho]

Hello. I was wondering if I could get some help with an issue I'm currently having. I'm trying to generate a ticket number system based on the ID column for an existing sharepoint list.

The new calculated column is titled "Ticket Number". It is calculated column. The formula used is as follows:

="TT"&[ID]

The formula seems valid, but whenever I go back to the list to see the results, every item in the list's value in the "Ticket Number" column is #VALUE!. In fact, I can't seem to get any expressions to work with a calculated column outside of simple math (addition/subtraction/multiplication on the ID value itself).

Could someone tell me what I'm doing wrong here?

[#2]

Make sure your column Data type returned is set to Text?

[#3]

@SuzanoSho - I was having a similar issue but resolved it. Make sure when you're in the List Setting for the calculated column, select the data type to return for the formula as a number (1, 1.0, 100). Hope this helps.

[#4]

It's always set to single line text

[#5]

Calculated don't even work for me from ID either right now, just stays blank until I go "save" the column settings again.

[#6]

I always use flow for stuff like this. Any reasons why you couldn't just create a flow to set that Value when a New item is created in the list?

[#7]

@SXT3410 This didn't work for me either.

[#8]

Well, the list is already populated with items. I'm not sure that kind of flow would work retroactively.

[#9]

@SuzanoSho First of all, SharePoint ID column is not supported in calculated column formula officially. But, it will not throw any error while you use [ID] in formula and try to save the column settings.

Anyway, I tried using the same formula you provided and it is working for existing items in list: 

But, it will not work for new items created in list (item ID 15) because ID cannot be used in calculated column in case of new items.

Also, when you will update any list item column (item ID 15), calculated column value will change & it will not have ID in it until you go to column settings and save the formula again: 

As per the How to use SharePoint ID field in Calculated Column? ,

The SharePoint ID field is not calculated in the calculated column until you manually update the formula in the calculated column field settings.

Conclusion: Avoid using ID in calculated column and use Power automate flow/designer flow instead.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#10]

@SuzanoSho Consider this:

1. For existing list items: create a power automate flow with manual trigger, read all item using "Get items" action and then update column value using ID from dynamic content for each list items - This will be one time activity only
2. For new items (in future): create a automated power automate flow with "When an item is created" trigger and use single "Update item" action which will update column value using ID from dynamic content

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.