INDEX (COLLECT) Formula returning #INVALID VALUE Error

Juan Ortiz · 09/12/23

I am trying to use INDEX COLLECT to pull data out of a specific column from another sheet based one three criteria.

My formula is:

=INDEX(COLLECT({Site Visit Intake - DOM Review %}, {Site Intake FDC-FW}, FW@row, {Site Visit Intake - DC}, DC@row, {Site Visit Intake - Metric}, [Metric Being Evaluated]@row),1)

I keep getting a #INVALID VALUE error despite ensuring that my source sheet has matching information.

Source Sheet:

Note that FW is feed from a formula, "DC" "Metric Being Evaluated" are drop-downs, and "DOM Review %" is a formula as well.

From Formula: Site Visit Intake - DOM Review % = "DOM Review %), Site Intake FDC - FW = "FW", Site Visit Intake - DC = "DC", and Site Intake - Metric = "Metric Being Evaluated:

Landing Sheet:

Any help would be greatly appreciated!

Paul Newcome · 09/13/23

What are the formulas for FW and %?

Juan Ortiz · 09/13/23

Hello Paul,

The FW column is a formula to turn a date into a fiscal week and year. (NOTE FY starts 4 weeks into calendar year)

=IF(ISBLANK(Date@row),"",(YEAR(Date@row)+""+IF((WEEKNUMBER(Date@row) - 4)<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row-4)))

The % Formula is just a formula to pull the info from whichever column it is in. The form is designed to ask a series of questions based on 5 different metrics with each metric having its own comment field. The form will only be filled out for one metric. So the column just pulls it to that column as an easy point to reference from

=IF(ISBLANK([Metric Being Evaluated]@row), "", IF([Metric Being Evaluated]@row = "Metric 1", [M1 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 2", [M2 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 3", [M3 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 4", [M4 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 5", [M5 Comments]@row))))))

Juan Ortiz · 09/13/23

As an update, I made a helper columns where I input the information manually to test if the formula and drop down source columns were the issue. The formula worked then.

As the source sheet is meant to be fed from the form and then referenced into another sheet via INDEX COLLECT, is there a way to get around the formulas and drop-downs through additional functions in the formula.

Paul Newcome · 09/13/23

I think I see where one potential issue may be...

How is the FW column populated in the Landing Sheet (the one containing the INDEX formula)?

Juan Ortiz · 09/13/23

It is populated as a text/number field. The landing page is an Insight Engine that is fed from a Metric Shuttle through automation.

The info that is fed into the metric shuttle is populated from a Tableau report which is exported into a in an excel format and copied into the metric shuttle sheet.

Paul Newcome · 09/13/23

What happens when you double click into one of the FW cells in the Landing Sheet? Is there a leading apostrophe there that is hidden from view when you are not editing the cell?

Juan Ortiz · 09/13/23

No apostrophe

Paul Newcome · 09/13/23

Ok. So your current formula:

=IF(ISBLANK(Date@row),"",(YEAR(Date@row)+""+IF((WEEKNUMBER(Date@row) - 4)<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row-4)))

is populating a text string that just looks like numbers, but the data coming into the Landing sheet is numeric data. Even though they look similar, you are comparing numbers to letters looking for a match.

You can convert the string output by the formula into a number using a VALUE function as below which should then mean you are comparing numbers to numbers and should get your match.

=IF(ISBLANK(Date@row),"",VALUE(YEAR(Date@row)+""+IF(WEEKNUMBER(Date@row) - 4<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row-4)))

Juan Ortiz · 09/14/23

The Positive is that the adding the VALUE to the FW formula from the Source page worked. Thank you very much for that.

Current issue is that the formula is not working consistently for every instance and I am trying to troubleshoot that issue. Below is a snapshot of my Source sheet. Note that rows with Yellow highlights signify that the landing page has an instance where the info should be pulled (Matches in the FW, DC, and Metric Being Evaluated Columns). The Green highlights signify that the landing page actually pulled the info and showed a result.

SOURCE PAGE:

LANDING PAGE

The first row is pulling from Row 3 of the Source sheet. Row 2 should be pulling from Row 2 of the Source sheet but it is not.

(NOTE that I wrapped the INDEX COLLECT formula in an IFERROR formula to return the ---- if no value is returned from the formula. The results in term of pulling was exactly the same before adding the IFERROR)