Highest Value of a Column in all Matching Rows

Hello Ramsay,

The MATCH function takes an optional argument that will find the highest value for you, the syntax looks like the following example: 

=MATCH("Task C", [Task Name]1:Done5, 1) 

The last 1 after the comma is an optional argument that tells the MATCH function to look for the highest value. Details on the function can be found on our formulas page: https://help.smartsheet.com/articles/775363-using-formulas

Let me know if this wasn't what you were looking for and I can take another look at your process.

Hello Ramsay,

Sorry for misunderstanding and thanks for the clarification. It appears that Smartsheet isn't capable of implementing this type of formula, the crux being the MAX(IF() portion of that VB formula in the stack overflow example—which Smartsheet can't do. We also don't seem to have a comparable workaround with our formulas that can achieve what you're looking for.

Sorry again if I got your hopes up! I'm going to pass this feedback along to our Product team for further review.

I did end up finding a "workaround" for this issue.  What I did was to create a new column ( far left of my data, hidden ) where I did my conditional logic and set a flag if the row met the conditions.  Then I pulled over the data into that column.   Then I used the MAX() function on that column.  It's not elegant, but it works. 

SS should continue to expand their function set to include advanced functions for all condtional logic, similar to SUMIFS, COUNTIFS, etc. 

As an example from above: 

Create New Column, FlagField

Fill FlagField with a formula    =IF([Task Name]1="A", [Value], "" ) 

Then you can use your the max function:  =MAX( [FlagField]:[FlagField]) 

Row #   Task Name        Value     FlagField

1               A                          2           2

2              B                          6            

3              A                          5           5

4              E                          8

5              F                          1

6              A                          3           3

HELLO;

I HOPE YOU CAN HELP ME WITH THIS PROBLEM:

I NEED TO SCRATCH A FORMULA TO GENERATE DEPENDENCIES AUTOMATICALLY IN A PROJECT SHEET WITH THOUSANDS OF ROWS.

EACH ROW CORRESPONDS TO A TASK REPEATED RANDOMLY. AND MY PROJECT HAS MORE THAN 70 DIFFERENT TASKS, SO IT IS VERY LABORIOUS TO ASSIGN DEPENDENCIES USING FILTERS BY TASK.

ATTACHED THE LINK OF MY SHEET EXAMPLE IN WHICH I HAVE TRIED TO SOLUTION IT USING THE FUNCTIONS:

LOOKUP + MATCH INDEX + MATCH

THANKS IN ADVANCE

https://app.smartsheet.com/b/publish?EQBCT=269e4f8a72d74b83a84357d386756959

<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="http://publish.smartsheet.com/fb9df41f04754198b5e93de66eef8b54"></IFRAME>

Thank you! That worked for my need!