Array Formula

    Hi All,


    Continuing my learning Phase, I have attached a workbook which shows companies by their contractual status. i.e. Main Contractor, Supplier etc.


    Tab "Store Details" lists sample company registration data
    Tab "All Data" lists the audit scores for all companies


    Tab "Data by Month" is where I would like to be able to filter data based on the selections made on the worksheet.


    I need some help to create an array formula that will populate the company names
    and to then lookup the data and populate the scores from the "All Data" tab


    I hope the work sheet explains this clearly.


    Thank you

    Your learning phase will never end, believe me! :)


    Is there any flexibility in the layout? You are making things very complicated for yourself by doing it in the way you have mocked it up.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Hi Ali,
    Unfortunately there is no flexibility in the structure. I am working around an old workbook which has vast amounts of data.
    I am adding the worksheet "Data by Month" into the old work book.


    Thanks for any help

    If it were me, I'd be updating things. It's never too late to modify!


    Hopefully someone will have the time today to help you with this, but I have run out, I'm afraid. It's not a quick job. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Merged cells is always a pain in the rear.....


    Start off with a "helper row" starting in D5 of Data By Month sheet, enter formula:


    =IF(D6="",C5,D6)


    copied across to end of last table. You can then hide row if desired.


    Then in D8 use formula:


    =IF($C8="","",INDEX('All Data'!$C$4:$CA$13,MATCH($C8,'All Data'!$B$4:$B$13,0),MATCH(D$5,'All Data'!$C$2:$CA$2,0)+COUNTIF($D$5:D$5,D$5)-1))


    copied across the first table and down to bottom including where there are blank dates.


    Then copy all those cells directly to next table, then next table and so on.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login