MAX COLLECT across a range

Paul Reeves

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 Newcome

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.

Paul Newcome

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.

Paul Reeves

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 Reeves

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