Auto Generated Number Referencing

Hi,

I noticed when using Index match on auto generated numbers something weird happens. On sheet A I use Index({desired result Sheet B}, match(sheet A @row, {search Criteria Sheet B},0),1) where Sheet A @Row is an auto generated number and search criteria sheet B is a text/number input. I found you have to use Index({desired result Sheet B}, match(Value(sheet A @row), {search Criteria Sheet B},0),1). This took some time to figure out but works nicely.

Now I have run into the issue where I am on Sheet B trying to reference sheet A but cannot VALUE reference a whole column ie(Index({desired result Sheet A}, match(sheet B @row, VALUE({search Criteria Sheet A}),0),1). Does anyone know a work around or has also run into this? For some reason I think auto generated numbers aren't coming in as number values.

Find more posts tagged with

Sheets

functionality

Cell linking

Accepted answers

All comments

Andrée Starå

Hi @Amanda Berta

That sounds strange!

I use that method all the time in my client solutions. (Auto-number matched to a manual number)

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Amanda Berta

hi @Andrée Starå

I ended up using =INDEX({Source Range 1}, MATCH(UPPER([Primary Column]@row), {Source Range 2}, 0)). There was a " ' " showing up when I copied the auto generated number which was causing the NO MATCH.

Andrée Starå

@Amanda Berta

Excellent!

Glad you got it working!

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

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.