Have a serial number column update when there is a response on another sheet with a new number.

EJ13

I have an inventory sheet that has a serial number column. I have a serpeate sheet used for tracking advanced exchanges where it populates a column for the old serial number and the new. I need a formula to identify the old serial number from the RMA sheet and match it to the inventory sheet. Then replace it with the new serial number. Additionally, the same component could break again and need to update the new serial number with the newer serial number, and so on. Finally, if there isn't a RMA for a serial number, there is no change on the inventory sheet.

I've tried, =IFERROR(INDEX({RMA New Serial Number}, MATCH([Component Serial Number]@row, {component serial number}, 0)), [Component Serial Number]@row)

This only works for the first time, say old SN 11111 is replaced with SN 22222, well if 22222 needs replaced, it doesn't recognize it to change it to 33333.

Also, with this formula, I didn't place it in the inventory serial number column, I placed it in a new column. I don't trust it to work if I place it in the component serail number column. But I ultimately need that column updated not the helper column I created.

Jason Albrecht

Hello @EJ13

You may like to try the INDEX(COLLECT()) option?

It is discussed here, along with VLOOKUP and INDEX(MATCH()) functions:

3 formulas to look up data in Smartsheet | Smartsheet

Since the Index/Collect option allows for multiple criteria to be used, you could have the second criteria to look for the latest edit date within the RMA, to ensure the latest serial number entry is used. This may overcome the Index/Match option working once only and not updating with new entries.

As for your second conundrum, I would trial auto update requests and/or approval requests.

For example, I would trigger an update request to be emailed to me/manager when the helper serial number column has been updated. The update request would allow me to copy the new serial number and paste it over the top of the existing/old number. While this is a manual process to functions such as REPLACE or SUBSTITUTE, and suitable for limited number of requests per day, it helps avoid having a formula in the Serial Number field and the related issues that come with that (such as the field becoming unusable/un-editable in a form).

Otherwise, you may like to use the 'New Component Serial Number' column as the default reference column and include in the formula to display the data in the 'Component Serial Number' column by default (i.e., if the Index/Collect formula doesn't return a valid result, then return the original serial number).

EJ13

https://community.smartsheet.com/discussion/comment/421432#Comment_421432

I can't get the index/collect to work. I also want to avoid having to approve the changes, as there could be a good amount each day.

EJ13

@Jason Albrecht I can't get the index/collect to work. I also want to avoid having to approve the changes, as there could be a good amount each day.

Jason Albrecht

Hello @EJ13

I'm sorry you can't get the index/collect to work.

Smartsheet have recently made AI available to help with these tricky formulas.

You can learn more and watch a demo here.

EJ13

@Jason Albrecht how would I do this part that you mentioned originally? "Otherwise, you may like to use the 'New Component Serial Number' column as the default reference column and include in the formula to display the data in the 'Component Serial Number' column by default (i.e., if the Index/Collect formula doesn't return a valid result, then return the original serial number)."

Jason Albrecht

Hi @EJ13

Going back over the comments, it looks like I was simply putting into words what your original formula was trying to achieve.

=IFERROR(INDEX({RMA New Serial Number}, MATCH([Component Serial Number]@row, {component serial number}, 0)), [Component Serial Number]@row)

That said, you will still want to make sure the formula for returning the new serial number is working correctly and not returning an error for anything else (this list for Formula error messages may help).

You said you couldn't get the index/collect to work. How did you go with Smartsheet's AI? If it provided a result that worked, would you be willing to share it with the community, for their ongoing learning? If it didn't work, feel free to provide the community with details of what you tried and what error messages you were getting, so we can help you further.

EJ13

Hi @Jason Albrecht

The formula you reference works on the initial run. Meaning if SN 1111 on the RMA sheet has 2222 at the same row, it updates on the inventory sheet. The issue we can't get past is if the RMA sheet then has 2222 as the old SN and 3333 as the new SN, the inventory sheet still only recognizes the first change from 1111 to 2222.

When I tried the AI this is the formula it gave me, which didn't work =IF([NewSN]@row = [OrigSN]@row, [NewSN]@row, [OrigSN]@row)