INDEX(COLLECT #INVALID VALUE error

Hi! I'm trying to solve a formula issue. I'm trying to pull text (Title) from a source sheet based on 2 criteria: Slot and Score. The slot needs to equal 1.1, and the score needs to match adscore@row. Here's the formula I created:

I am getting a #INVALID VALUE error message.

Other background information:

{Title}, {Slot}, and {Score} each reference one corresponding column within the source sheet.

I have tried the Slot criterion both with and without quotation marks.

I have ensured that there is data that matches all of the criteria listed.

Help please! I've been stumped for days.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Is that error present in even a single cell within any of the ranges referenced?

Heather Duff

@Paul Newcome ,

Unfortunately (or fortunately?), no:

Destination:

Source (where Timeslot = {slot}, Score = {score} and Session Title = {title}:

(The list goes on for a few more rows, but without error.)

I appreciate you taking a look at this.

Paul Newcome

Hmm... I can say that you are definitely going to need the quotes around "1.1". No quotes means it is looking for a number. Quotes means it is looking for text, and it looks like the source sheet is housing it as text.

The error not being present in the source sheet leads me to believe that the formula is not finding a match, but I can see right there in your screenshots that there definitely is one.

Let's give this a try... Completely remove the formula from the sheet. Log out. Clear cookies and cache. Log back in. Manually retype the formula.

I'll keep my fingers crossed for you. 🤞

Heather Duff

😂 @Paul Newcome Did you just give me the Smartsheet equivalent to "try unplugging it, wait 10 seconds, then plug it back in"?

In all seriousness though, thank you for taking a look, and for validating that it should be working as is. I'll keep trying, and will try what you said. I'll let you know.

I also roped @Andrée Starå into this mess on LinkedIn and have shared the sheets with him, so hopefully he'll find a glaringly obvious issue that we've missed.

Teamwork makes the dream work...I appreciate your wisdom!

Paul Newcome

@Heather Duff Haha. Yes I did, but I have had it work on quite a few occasions when there was some issue with latent data on the back end of the sheet. It basically resets the sheet.

JohnEvers

After 2 hours of trying I tried your "Let's give this a try... Completely remove the formula from the sheet. Log out. Clear cookies and cache. Log back in. Manually retype the formula.". Shockingly it worked! Thanks.

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.