Return Value based on MAX Date + Criteria

CJ Dijkstra
CJ Dijkstra ✭✭
in Formulas and Functions

Hi All,

I have a database from which I would like to retrieve summary information.

Sample database:

image.png


Sheet 2 is my summary sheet in which I need to pull the values based on the MAX date in the database:

Sheet 2:

image.png

I would really appreciate some help on possible formula suggestions.

Thanks

Chris

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @CJ Dijkstra

    You'll need to add your two criteria to the MAX portion of your formula as well, otherwise it just looks for the MAX date in the entire sheet instead of the MAX date for that Fleet No.

    Ex:

    =INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX(COLLECT({Date}, {Fleet No}, [Fleet No]@row))), 1)


    If this still gives you an error in your other sheet, check each of the ranges to make sure that there are no #INVALID VALUE errors housed in a cell in any of those referenced columns:

    {Value}

    {Fleet No}

    or

    {Date}

    If there's a cell with an error in any of these columns, this will cause the formula referencing these columns to error as well.

    Let me know if this worked/helped!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • CJ Dijkstra
    CJ Dijkstra ✭✭

    @Paul Newcome Sorry to direct message you on this one but I would really like to have your input on this.

    My formula as follows: =INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX({Date})), 1)

    This formula works as expected - providing 30, 2320 and 1520.

    However, when I am doing the same in a different environment (more complex data set) I get a #INVALID VALUE - now I suspect this is got something to do with the range etc. but I cant get to the bottom of this.

    Many Thanks

    CJ

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @CJ Dijkstra

    You'll need to add your two criteria to the MAX portion of your formula as well, otherwise it just looks for the MAX date in the entire sheet instead of the MAX date for that Fleet No.

    Ex:

    =INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX(COLLECT({Date}, {Fleet No}, [Fleet No]@row))), 1)


    If this still gives you an error in your other sheet, check each of the ranges to make sure that there are no #INVALID VALUE errors housed in a cell in any of those referenced columns:

    {Value}

    {Fleet No}

    or

    {Date}

    If there's a cell with an error in any of these columns, this will cause the formula referencing these columns to error as well.

    Let me know if this worked/helped!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • CJ Dijkstra
    CJ Dijkstra ✭✭

    Hi @Genevieve P.

    Thank you very much - it makes 100% sense and this solved my problem!! I really appreciate your assistance with this.

    Kind Regards

    Chris

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad I could help 🙂

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!