INDEX COLLECT across a range

Paul Reeves

Reposting now that I have a better understanding of my problem.

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.

I understand I should be using INDEX COLLECT but still struggling with the structure.

=INDEX(COLLECT({Forecast worksheet: Quarters}, {Forecast worksheet: Entry Date}, [Last Submission Date]@row, {Forecast worksheet: Sales Mgr/ASD}, [Sales Mgr/ASD]@row), [LOB#]2 + VALUE(RIGHT([Q1]1, 1)))

I would appreciate any assistance.

Leibel S

@Paul Reeves

Try this:

Add a helper column in your form/data sheet called [Sales MGR Lookup]. It should be formatted same as the [Sales Mgr/ASD] column.

it should have a formula: =IF(MAX(COLLECT([Entry Date]:[Entry Date], [Sales Mgr/ASD]:[Sales Mgr/ASD], @cell = [Sales Mgr/ASD]@row)) = [Entry Date]@row, [Sales Mgr/ASD]@row, "")

Add a blank row on top of your sheet that is filled in with your column titles (Core Q1... WWA Q1... etc.)

Then in your summary sheet you should be able to get the data as follows:

=INDEX({Forecast worksheet: Quarters},MATCH([Sales Mgr/ASD]@row,{Forecast worksheet: Sales Mgr Lookup},0),MATCH(PARENT([Sales Mgr/ASD]@row)+" " +[Q1]1,{First Row of Forecast sheet},0))

It could sound complicated, but should work...

Paul Reeves

@Leibel S ,

Thank you. I kind of moved on to a different set up but I will make sure I give this a try. Your right it does look a little complicated but I am excited to give it a whirl.

Paul