Index Collect Formula Showing #Invalid Value

Sarah123

I've been searching around and my formula looks correct. I even saw on a previous discussion that you should erase the formula and then log out and log back into Smartsheet and maybe that will help (unfortunately it didn't for me). I've also tried to make the formula manually multiple times. The only thing I can think of is that the person's ID number would show up multiple times (because they have multiple days off in the sheet). Here is my formula: =INDEX(COLLECT({Hours}, {Week}, "08/14/22", {Resource Time Off SAP Range 1}, ID@row), 1)

There are no errors on the other sheets - it's just a list of the people with their ID numbers, which week ending they have taken time off for and how many hours is PTO during that week.

Sarah123

In case anyone stumbles on this thread later on - We got it to work! The correct formula is =INDEX(COLLECT({Hours}, {Value ID Column}, value@row, {Week}, =DATE(2022, 8, 14)), 1) . The ID number needs to be a helper column with =value([ID Number]@row) because of the leading zeros we have on some IDs. And the date column in the SAP pull needs to be a date column type.

Paul Newcome

That is the INDEX/COLLECT version of #NO MATCH. How is the data in the {Week} range populated? If it is actual dates, try using a DATE function for the criteria.

DATE(yyyy, mm, dd)

Sarah123

It wasn't a date column (I changed it to text/number). I just tried to update the column in the SAP sheet to date and I'm still getting #Invalid Value still in the field with the updated formula =INDEX(COLLECT({Hours}, {Week}, DATE(2022, 8, 14), {Resource Time Off SAP Range 1}, ID@row), 1) . I know that I should have something coming back as there is one person that has time off during that week. The date in the SAP sheet is written as 08/14/22 - does that need to be updated for the full year or should it be showing as another way?

Paul Newcome

If it was a text/number column then your first should be working. How is the ID column populated? What kind of data is that? Are there some entries with leading zeros and some without? Are you able to share some screenshots?

Sarah123

The ID column is the person's company ID (some have leading zeros while others do not - it depends on when they were hired). This is a text/number column. This is the sheet that I'm trying to get the index collect formula to work.

This is the sheet that is pulled out of SAP and shows all the PTO hours that have been entered (the first 3 rows are for the same person so I'm not sure if this is what is causing the issue since the ID is the same since it's the same person). I've had the week as a date and also as a text/number field and neither seems to work.

Paul Newcome

Ok. Because some have leading zeros and some do not you actually have two different data types in the ID column (text with leading zeros and numerical without). Insert a helper text/number column (can be hidden) with the following column formula:

=[ID Column Name]@row + ""

This will convert everything into text values. Then set up your INDEX/COLLECT to evaluate this column instead of the original ID column.

Sarah123

Unfortunately it still isn't working. I've also tried a couple different ways of having the formula written out - not sure if both are setup wrong and that's causing the issue??

=INDEX(COLLECT({Hours}, {ID2}, [ID to Data]@row, {Week}, "08/14/22"), 1)

=INDEX(COLLECT({Hours}, {ID2}, [ID to Data]@row, {Week}, DATE(2022, 8, 14)),1)

Paul Newcome

Have you double checked your ranges? I know sometimes if I am moving too fast when creating a cross sheet reference I will click on a column header to select the entire column before the sheet fully loads in the popup window. When the sheet does finally fully load, it automatically reverts back to the "home cell" (top right corner of the sheet).

Are you able to enter a fake ID and try to match on it (one with leading zeros and one without) and provide screenshots of that?

Sarah123

I've quadruple checked the references (and remade them multiple times). If I put in the person's name instead of their ID number I can get it to work, however, names change and we'd rather go by the ID number. I've tried with leading zeros and without, using the original column and also using the column with the ID number + "" .

Paul Newcome

Did you try putting that column that converts the IDs to text in BOTH sheets and referencing them so that you are DEFINITELY comparing text to text?

Sarah123

Yes, I made new columns and have them setup as text to reference.

Paul Newcome

Unfortunately I am going to have to suggest reaching out to Support as everything SHOULD be working.

Sarah123

Thanks - I appreciate your assistance. 😀

Sarah123

In case anyone stumbles on this thread later on - We got it to work! The correct formula is =INDEX(COLLECT({Hours}, {Value ID Column}, value@row, {Week}, =DATE(2022, 8, 14)), 1) . The ID number needs to be a helper column with =value([ID Number]@row) because of the leading zeros we have on some IDs. And the date column in the SAP pull needs to be a date column type.