Cross sheet formula to return a date based on drop down category

Meredith Rhodes

Hello!

I'm super familiar with the INDEX(MATCH) function to copy information from a cell in another sheet based on a unique identifier.

But I'm struggling to figure out a cross sheet formula to return a date IF a certain drop down item is selected.

Here is what I am looking to do:

I have a Unique ID column to Match, I want to Index the Submission Date column IF the Action Category column is 'Reg Docs'.

Here is my attempt:

=IFERROR(INDEX((IF({Action Category} = "Reg Docs", 1, 0)), {Submission Date}), MATCH([Unique ID]@row, {Unique ID}, 0)), "")

^^^ This is Unparseable - can anyone help me make this work?

Thank you!

Meredith

Samuel Mueller

@Meredith Rhodes You should try index/collect since you are trying to use multiple criterias.

=iferror(index(collect({submission date}, {action category}, [action category]@row, {unique id}, [unique id]@row),1),"")

Meredith Rhodes

Thanks, @Samuel Mueller -

I'm less familiar with Index / Collect - the action category can be several things, how do I point it to only returning submission dates for 'Reg Docs' for a match on Unique ID? Do I have to include the following?

=iferror(index(collect({submission date}, {action category}, [action category]@row = "Reg Docs", {unique id}, [unique id]@row),1),"")

Samuel Mueller

@Meredith Rhodes in this case you would only need the quotes. Collect is very similar to a SUMIFS, COUNTIFS, etc, where the syntax is (range, criteria). Smartsheet links/examples below

=iferror(index(collect({submission date}, {action category}, "Reg Docs", {unique id}, [unique id]@row),1),"")

the  [action category]@row piece that I had in my earlier formula makes it dynamic, so it would look at what value you have in the Action Category column. In the 126 row it would match "Reg Docs" and return the value that matches unique id 126 and action category "Reg Docs", but in the 83 row it would have returned the result that matched the unique id 83 and the action category "Change Misc". Does that make sense? If you hard code it as seen in the formula from this post to "Reg Docs" then in the 83 example it would look for unique ID 83 with the action category "Reg Docs" still.

COLLECT Function | Smartsheet Learning Center

SUMIFS Function | Smartsheet Learning Center

COUNTIFS Function | Smartsheet Learning Center

Meredith Rhodes

Thanks again @Samuel Mueller -

I'm getting an 'incorrect argument' error when I use this formula:

=IFERROR(INDEX(COLLECT({Submission}, {Action}, "Reg Docs", [Unique ID]@row, {UID}, 1)), "")

I'm still not quite sure I understand this.

"Submission" is the column I want to return a date from. "Action" is the column where we'll find "Reg Docs", [Unique ID]@row, and {UID} are the numbers I need to match between the two sheets. <<< Are they working to match in this formula?

Does "1" means "YES populate the submission date if you can find all of these things to be true?"

Thanks for talking this out with me.

Samuel Mueller

@Meredith Rhodes like this, you flipped a range and criteria. Make sure whatever is in the curly brackets {} matches your cross sheet reference names.

=IFERROR(INDEX(COLLECT({Submission}, {Action}, "Reg Docs", {UID}, [Unique ID]@row), 1), "")

• INDEX(COLLECT({Submission}, {Action}, "Reg Docs", {UID},[Unique ID]@row), 1)
• Collect
  • {Submission} - Range to return
  • {Action} - First Criteria Range
  • "Reg Docs" - first criteria to match, matches in the {action} range
  • {UID} - second criteria range
  • [Unique ID]@row - second criteria to match, matches in the {UID} range
• 1 - returns the first value in the collect array (should only be 1 value to return, if unique)

Make sure you pay attention to the parenthesis and commas

• =IFERROR(
  • INDEX(
    • COLLECT({Submission}, {Action}, "Reg Docs", {UID}, [Unique ID]@row)
  • , 1)
• , "")

Meredith Rhodes

Thank you so much for this explanation @Samuel Mueller! This makes sense - and I can apply this logic to other cases....

And it worked!

I appreciate the time you took on this!

Meredith

Samuel Mueller

Happy to help! Good luck 😊

Meredith Rhodes

Ope! @Samuel Mueller - I have a date column and I'm trying to populate it with a date from another sheet.

I see the date that is supposed to come over, but it isn't. When I strip the IFERROR statement away, it says 'Invalid value'.

=INDEX(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial", {UID}, [Unique ID]@row), 1)

This one I'm collected the DATE Processed Approval where the committee is 'Reliant' and the Action is 'Initial'... is there anything special about Dates with Index / Collect?

Samuel Mueller

@Meredith Rhodes shouldn't be anything special in this case. It seems like it is not finding anything that matches the criteria.

try a couple things to make sure it works

=JOIN(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial", {UID}, [Unique ID]@row), ",")

This one will return any matching values

=JOIN(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial"), ",")

This one removed a criteria to see if it returns anything at all.

Meredith Rhodes

Thanks @Samuel Mueller -

I tried the first formula - and it didn't return anything (the cell I'm looking for has a date in it - 10/25/23)

The second formula returned a bunch of dates - so it seems to be doing something.

Here is a closer look at my process:

I'm trying to write a formula on another sheet to return this data. Unique ID matches the other sheet, where Committee Submitted to is 'Reliant', Action Category is 'Initial', and I'm looking for the Submission Date of 10/18/23 for one formula - and the Date CRO Processed Approval of 10/25/23 returned for the other formula.

To return the Approval Date:

=INDEX(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial", {UID}, [Unique ID]@row), 1)

is returning #Invalid Value. Can you see anything wrong with this formula?

Samuel Mueller

@Meredith Rhodes I'm not sure if this will fix it, but try this

=INDEX(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial", {UID}, @cell=[Unique ID]@row), 1)

The unique ID on the other sheet is in the same format right? '0249' with the leading 0?

Meredith Rhodes

=INDEX(COLLECT({Processed Approval}, {Committee}, "Reliant", {Action}, "Initial", {UID}, @cell=[Unique ID]@row), 1)

That was it! I didn't have the @cell=

What a journey this is :)

Thank you!

Meredith

Samuel Mueller

Technically, it shouldn't need the @cell, but I actually ran into the same issue on a formula today, and that fixed it lol. Let me know if you need anything else!