INDEX COLLECT across a range

Paul Reeves
Paul Reeves Overachievers
edited 02/17/24 in Formulas and Functions

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.

paul e. reeves

Principal Business Analyst

HMH

Tags:

Answers

  • Leibel S
    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
    Paul Reeves Overachievers

    @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

    paul e. reeves

    Principal Business Analyst

    HMH

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!