Forum Discussion

marietuttle1973's avatar
marietuttle1973
Brass Contributor
Dec 17, 2024

Help with formula in Exc

I have a spreadsheet with multiple tabs at the bottom.  On the Master I have one column with drop down list.  I want the info on the master tab to feed over to the tab corresponding to the tab that matches the drop down item.  Spreadsheet attached.

Example - The first person is a "lead".  Column B contains the drop down list.  I want that information to feed over to the tab at the bottom that says leads.

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    Yeah, did not consider if you're using an older version. Do this instead:

    Type manually the sheet name on the cell of that sheet wherever you like, in my case it's on cell N1 of Lead sheet,

    Use this array formula on cell A2 (double click the cell to enter in Edit mode) of Lead sheet:

    =IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"")

    Upon putting the formula on the cell press CTRL + SHIFT + ENTER, instead of pressing enter.

    Then drag that formula to column H and then drag it again to bottom of your table.

    • marietuttle1973's avatar
      marietuttle1973
      Brass Contributor

      That didn't do anything - it just put the formula in A2 of the lead sheet but didn't return any data?

      • Rodrigo_'s avatar
        Rodrigo_
        Steel Contributor

        As long as you put it right it should work by now..
        Put the sheet name on cell N1, then
        the formula must have a bracket {=formula} at the beginning and at the end, like this:
        {=IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"")}
        then drag the formula across the table

        Master Sheet:

        Lead Sheet:

        Install Sheet:

         

  • I copied your formula into my spreadsheet and it says that function isn't valid and highlights the word filter.  I assume it's because of the version of excel?  How do I get around this?

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    The "Type" column from master sheet must be match with the other sheet's name,

    Then, use mid and cell function to extract your sheet names, and use filter to return dynamically the data from master sheet, and will update whenever you input more data on the master sheet.

    Put this formula to cell A2 of the other sheets. =FILTER(Master!A2:H15,Master!B2:B15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"No data")

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor

      I would only note that the formula CELL doesn't work on Excel online or certain other platforms so if compatibility is needed I would suggest just inserting a row at the top and then in Cell A1 put the sheet name and then adapt the above formula to just look at cell $A$1 for the sheetname

      Then on the Master you can automatically generate those sheetnames for your data validation using 

      =VSTACK('Lead:Service Call Form'!A1)

      see attached

       

    • marietuttle1973's avatar
      marietuttle1973
      Brass Contributor

      I copied your formula into my spreadsheet and it says That function isn't valid - it is highlighting the word FILTER.

      • m_tarler's avatar
        m_tarler
        Bronze Contributor

        Yes sounds like a problem with the Excel version.  I'm guessing you are using something older than Excel 2019.

        Other alternatives include pivot tables and power pivot and some much more complicated formulas that I would avoid.  Attached I included an example of a pivot table.  There are many ways to configure and format the pivot table but I'll leave that up to your discretion.

        Also I want to point out that I formatted the original table as a Table (but you could also just turn Filters On) and now you can easily use the drop down triangles to Filter the data based on the Type column and you don't need all the other tabs.

Resources