Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭✭✭✭
    in Formulas and Functions

    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:

    reg docs.PNG

    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

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Overachievers

      @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),"")

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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),"")

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers
      edited 11/02/23

      @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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers
      edited 11/02/23

      @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)
      • , "")


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers

      Happy to help! Good luck 😊

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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?

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers

      @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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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:

      example.PNG

      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?

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers
      edited 11/03/23

      @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?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      =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

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter
    • Overachievers

      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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions