Unique codes

Hey all,

I am trying to create a system that will check a unique code that is entered through a form and display if this code has been used already or not.

I have a formula in the summary sheet that will asses if the code has been used or not:

=VLOOKUP($Code$1, {Z Codes Trial Database Range 1}, 3, 0)

The problem is that I would like the formula to always check the most recent entry but the formula changes when a new row is added at the top of the sheet even though its locked.

Is there a way to lock the formula even when a new row is added?

Any other ideas on how to build a unique code system in Smartsheet would be great.

Thank you,

Find more posts tagged with

Forms

Formulas

Accepted answers

Paul Newcome

Try nesting in an INDEX function with a specified row of 1.

=VLOOKUP(INDEX(Code:Code, 1), {Cross Sheet Reference}, .......)

Ramzi

Try using INDEX(Code:Code, 1) instead of $Code$1

Note: Also, I would recommend you use INDEX/MATCH instead of VLOOKUP. Much more reliable.

I hope this helps you.

All comments

Paul Newcome

Try nesting in an INDEX function with a specified row of 1.

=VLOOKUP(INDEX(Code:Code, 1), {Cross Sheet Reference}, .......)

Ramzi

Try using INDEX(Code:Code, 1) instead of $Code$1

Note: Also, I would recommend you use INDEX/MATCH instead of VLOOKUP. Much more reliable.

I hope this helps you.

Itai Perez

Amazing,

Thank you both, it works!

@Ramzi - Why is index match better than Vlookup?

Ramzi

The challenge with VLOOKUP is it specifies a column number. If you change the column order or add a column to the reference sheet, it could throw off your formula and return incorrect information.

Paul Newcome

Happy to help. 👍️

INDEX/MATCH is better than VLOOKUP for a number of reasons that come from the ability to reference only the two columns needed. First it allows for more flexibility because by referencing the columns individually, it doesn't matter what order they are in.

It also reduces the number of cells being referenced by cross sheet references (potentially) because you do not need to reference every column in between the two columns you want to reference. This has the added (potential) benefit of better performance since you are only referencing two columns instead of the entire sheet.

Itai Perez

@Paul Newcome

Its time Smartsheet added Xlookup :)

Paul Newcome

@Itai How is XLOOKUP any different than an IFERROR/INDEX/MATCH?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.