How would I use an Index/Collect formula to include Child Row data as one of my criterion?

jkt2001

For example, I would like to collect a certain column based on a specific text string in another column, however that text string is located in a child cell/row which I believe is the reason I am receiving an "Invalid Value" error. I have two separate criterion in my formula, and I removed this text string portion to isolate the issue, and as I suspected the collect formula worked in this case.

INDEX(COLLECT({Column I want to pull from},{Column with criterion 1}, cell@row criterion, {Column with criterion 2}, "text string I want to match and pull the adjacent column against"),1))

Thanks!

Samuel Mueller

Wonderful, yeah I misread your original question then it hit me what you meant. Let me know if you have any other questions.

Samuel Mueller

@jkt2001 I have a question, are all three of these references on the same sheet, and include the entire column?

{Column I want to pull from}

{Column with criterion 1}

&

{Column with criterion 2}

Also please send a copy of your actual formula that is returning the "Invalid Value"

jkt2001

INDEX(COLLECT({Date Job Completed}, {Account Number}, [Account Number]@row, {Task Name}, "Tiling"), 1))

Yes all of the references are on the same sheet. Tiling task name is a child row under the Account number Parent Column

Samuel Mueller

@jkt2001 ok just want to make sure you don't actually have an extra parentheses here:

=INDEX(COLLECT({Date Job Completed}, {Account Number}, [Account Number]@row, {Task Name}, "Tiling"), 1)

and the column this formula is in is a date column, correct?

jkt2001

Thanks! Yup it's a date column, if I just do the below formula I get it to work, but I don't have the Task Name criteria:

=INDEX(COLLECT({Date Job Completed}, {Account Number}, [Account Number]@row), 1)

And sorry, had an extra column in here but not in smartsheet, please disregard

Samuel Mueller

So, I would double check that reference {Task Name} and make sure it's actually referencing the entire column. Sometimes references unselect if you do it quickly.

If you're not sure how to check: Go to manage references, find that reference, click the 3 dots and hit edit reference to make sure.

Samuel Mueller

and if that is working, try the flip

=INDEX(COLLECT({Date Job Completed}, {Task Name}, "Tiling"), 1)

and see what you get.

jkt2001

Yup everything looks to be referenced correctly. You don't think having the "Tiling" text in the child rows of the cross referenced sheet is the cause of my "Invalid Value" error? I think what it's telling me is it doesn't find any matches.

jkt2001

Samuel Mueller

Should not matter that the row is a child as long as it's in the right column.

Samuel Mueller

wait never mind, Your formula has to match all criteria in the same row.

If that's what you mean by it's in a child row, then yes you will need a different formula and a column helper.

jkt2001

Bingo, I can't believe I didn't catch this. The first criterion text is not populated in the child rows. Thank you! We can mark this as resolved.

Samuel Mueller

Wonderful, yeah I misread your original question then it hit me what you meant. Let me know if you have any other questions.

jkt2001

Thank you, no I worded my question poorly. I appreciate it!