Index/Collect Formula Error
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!
Answers
-
Is that error present in any cell within any of those ranges?
-
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.
-
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.
-
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.
-
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.
-
Ah. Yes. If "Q1-2021" is not in the source data then you would get an error.
-
It's in the source. It's not in the target.
I figured it out though. Thanks!
-
That would do it too. Haha. Glad you got it sorted, and I am always happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
