Index Collect Formula Showing #Invalid Value

Options

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.

Tags:

Best Answer

  • Sarah123
    Sarah123 ✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sarah123 ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sarah123 ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sarah123 ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sarah123 ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sarah123 ✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Sarah123
    Sarah123 ✭✭✭✭
    Options

    Thanks - I appreciate your assistance. 😀

  • Sarah123
    Sarah123 ✭✭✭✭
    Answer ✓
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!