Index/Collect Formula Error

alexis.ray89371

Hi,

I am receiving the error "#INVALID VALUE" on only the first row of my data and wondering if someone could help with where I went wrong in my formula?

=INDEX(COLLECT({2021 KPI Scores Range 1}, {2021 KPI Scores Range 3}, @cell = [KPI #]@row, {2021 KPI Scores Range 4}, @cell = Region@row, {2021 KPI Scores Range 2}, "Q1-2021"), 1)

I am trying to pull in the value in the "Actual" column into the "Q1" column in yellow where "KPI#" AND "Region" are the same in both sheets and where Period "Q1-2021".

Thank you!

Paul Newcome

Is that error present in any cell within any of those ranges?

alexis.ray89371

Hi,

No, it does not. there are only 90 rows of data so far in the source sheet so it was easy to look in just those referenced columns for errors and there weren't any.

Paul Newcome

Try adding in an @cell reference to the last criteria set for the quarter. It shouldn't make a difference but I have noticed that sometimes it does.

Also double check your ranges to ensure they are referencing entire columns. I have also noticed that sometimes it is possible to select a range for a cross sheet reference too fast. When the window opens that shows the sheet you want to reference, give it a few extra seconds to load completely. There have been times where I would quickly select a column header as soon as the sheet popped up only to have it revert (without warning) back to the "home cell" (top left corner of sheet) once the reference sheet was finished loading a split second before I clicked on the button to create the reference.

alexis.ray89371

The quarter is not located in the target sheet as data in the row. There is a column per quarter. So, where would the @cell reference go in the formula? I have just known to use the @cell if I am trying to match data that is in both sheets in the row data. In my case quarter isn't in the row data in the target sheet. I may just not fully understand the how to use the @cell yet if I am misunderstanding. :)

As you suggested, I did go into the formula and did "edit reference" to validate that all my references were pulling the correct column and didn't do anything weird and everything looks good.

I'm stumped. Lol.

alexis.ray89371

Not sure if this helps...but, I went ahead and copy and pasted the formula into the other quarter columns and just changed the "Q1-2021" in the formula to the corresponding quarter like "Q2-2021" etc.

It did the same thing on row 1 for Q2 column. Q3 an dQ4 all have the error because those quarters are not in the source data yet so makes sense.

Paul Newcome

Ah. Yes. If "Q1-2021" is not in the source data then you would get an error.

alexis.ray89371

It's in the source. It's not in the target.

I figured it out though. Thanks!

Paul Newcome

That would do it too. Haha. Glad you got it sorted, and I am always happy to help. 👍️