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 m...
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Dec 18, 2024

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
Dec 19, 2024

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_
    Iron Contributor
    Dec 19, 2024

    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:

     

Resources