MAX COLLECT across a range
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
Principal Business Analyst
HMH
Best Answer
-
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.
Answers
-
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.
-
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
Principal Business Analyst
HMH
-
@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
Principal Business Analyst
HMH
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!