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.

    Nested formula with sheet references help

    ✭✭
    in Formulas and Functions

    I need a formula that will: 


    look up a name listed on sheet #1 on Sheet #2 and pull the most recent date they completed training. So far I have tried two formula combinations and neither has worked.


    =IF({Training Data Range 1 - Name} = Name@row, MAX({Training Data Range 2 - Date}))  #INVALID OPERATION


    =VLOOKUP(Name@row, {Training Data Range 3}, MAX({CIV CJIS Training Data Range 2 - Date}), false)   #INVALID ARGUMENT SET

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭✭

      Try this,

      =MAX(COLLECT({Training Data Range 2 - Date}, {Training Data Range 1 - Name}, Name@row))


      · Share on FacebookShare on Twitter
    • ✭✭

      So I put that in and I'm no longer receiving an error message, but it's not pulling the date in--Just blank.

      · Share on FacebookShare on Twitter
    • Employee

      Hi @collettee

      Is it possible that either the "Name" isn't finding a match across your sheets, or that the there is a blank cell associated with that name instead of one with a date?

      Try using a COUNTIF formula to check and make sure that the "Name" is matching:

      =COUNTIF({Training Data Range 1 - Name}, Name@row)

      If this is returning 0, then the formula can't find a match for that Name. Make sure that the spelling is exactly the same across sheets, including spaces.

      If the number is correct in this COUNTIF, check to make sure that your {Training Data Range 2 - Date} is looking at the correct column, that the column is set to date type, and that there are dates associated with that person.


      If none of this has helped, can you provide a screen capture of your source sheet, but block out sensitive data?

      Cheers,

      Genevieve

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · 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