Forum Discussion

11delta's avatar
11delta
Copper Contributor
May 04, 2022

excel equation help

hi there, im reasonably new to the excel world so am looking for some guidance.

 

i am creating a multi sheet work book with configurable cell content (text) via drop down boxes on each sheet  in a given set of 10 cells there would be a numerical value in an adjacent cell . each corresponding 10 cell group  in each sheet may have different  text content  depending on what has been selected from the drop down list .  i would like to collate  all the content  from each configurable cell by its text name or content not necessarily by cell number along with its corresponding  value and do this across multiple sheets. 

 

does anyone know if this is possible and if so how can i do this?

 

Thanks in advance

excel

3 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    May 04, 2022

    Hi 11delta 

     

    I'm sure there are several ways to do this. But to give a proper advise, more information is required. Maybe you can provide an example file with some data and desired results (or at least some screenshots).

    Also, please let us know which Excel version you are using, and on which platform (Windows, Mac)

    • 11delta's avatar
      11delta
      Copper Contributor
      May 04, 2022

      Thanks Year to date data collationQuarter 1Quarter 2Thanks Martin_Weiss  Ive hav uploaded some screen shots, hopefully this demonstrates what i would like to do. 

      the first image shows all available colours for the year, second shows what colours have been selected for that quarter and the quantity of each.

      the third image shows the same but includes different colours from the previous.

       

      i wish to collate total number of colours selected for the year to date regardless what cell they may be placed in. i hope this can be done, i have a sample file but unsure how to upload on this forum

       

      im using excel for Mac version 16.6 bu can also do this on windows using version 1908

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 05, 2022

        11delta 

        Here is a way to do it:

        Create a list of the sheets you want to sum.

        Name this range Sheets. In the screenshot below, it is E2:E5.

        In B2, enter the formula

         

        =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!" & "C4:C9"),A2,INDIRECT("'"&Sheets&"'!" &"D4:D9")))

         

        Here, C4:C9 is the range with the colours and D4:D9 the range with the quantities (these should be the same on all sheets).

        Fill down.

         

         

        Sample workbook attached.