# MAX COLLECT across a range

Overachievers
edited 02/13/24

I am a bit stumped and looking for a little help.

I have a forecast sheet with a form where the user has to submit their monthly forecasts for the quarters across five subject areas. I have a column for each quarter and each subject area, 20 columns is my range, Forecast worksheet: Quarters

For example purposes please assume the user would populate all 20 columns.

On a summary sheet I want to capture the last entry for each submission, across the range. I am able to get what I want with separate cross sheet references on all the quarters and subject area, ideally, I would like to write one formula that solves this.

Quarter/Subject =MAX(COLLECT({Forecast worksheet: WWA Q1}, {Forecast worksheet: Entry Date}, [Last Submission Date]@row))

Goal =MAX(COLLECT({Forecast worksheet: Quarters}, {Forecast worksheet: Entry Date}, [Last Submission Date]@row, {Forecast worksheet: Quarters}, .......)) - I just don't know how to set the second criteria

I would appreciate any assistance.

paul e. reeves

HMH

Tags:

• ✭✭✭✭✭✭

That isn't going to be possible with the MAX function. If you flip things around so that the most recent submission is at the top of the sheet, you can use an INDEX/COLLECT to reference the entire range and a nested formula in the column portion of the INDEX function to indicate which column to pull from.

• ✭✭✭✭✭✭

That isn't going to be possible with the MAX function. If you flip things around so that the most recent submission is at the top of the sheet, you can use an INDEX/COLLECT to reference the entire range and a nested formula in the column portion of the INDEX function to indicate which column to pull from.

• Overachievers

Hi Paul,

Unfortunately, I think I am following but am having some difficulties. I sorted the Forecast worksheet, descending. In my snippet below I am trying to pull the 3rd column. Like you mentioned I would probably use a nested statement to make it a little more dynamic.

#INCORRECT ARGUMENT SET

=INDEX(COLLECT({Forecast worksheet: Quarters}, {Forecast worksheet: Entry Date}, [Last Submission Date]@row), 1, 3) "3rd column from Quarters range"

paul e. reeves

HMH

• Overachievers

@Paul Newcome - you probably have not seen my post above.

Hi Paul - I don't mean to be so dense but I am not quite following the logic to set up the INDEX/COLLECT, specifically "column portion of the INDEX function". I am hoping you can help guide me a bit.

Below is my latest attempt of many...

=INDEX(COLLECT({Forecast worksheet: Quarters}, {Forecast worksheet: Entry Date}, [Last Submission Date]@row, @cell), 3)

paul e. reeves

HMH

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!