Forum Discussion

Aleisha2125's avatar
Aleisha2125
Copper Contributor
Aug 21, 2019

Help please

I have two worksheets. Let’s say A & B, on work sheet b there is a column with a list of products and beside it a column where you enter the qty of the product you want. On work sheet A I want the col...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 21, 2019

Aleisha2125 

Perhaps VLOOKUP or INDEX/MATCH. Could you submit small sample file to illustrate how data is organised?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 22, 2019

    Aleisha2125 

    Here

    in D5 you may enter

    =IFERROR(
   INDEX('Worksheet B'!$C:$C,
      AGGREGATE(15,6,
         1/('Worksheet B'!$E$3:$E$100<>0)*ROW($A$3:$A$100),
         ROW()-ROW($D$4))
   ),
"")

    and drag it down till empty cells appear. 

    • Aleisha2125's avatar
      Aleisha2125
      Copper Contributor
      Apr 09, 2020

      Hi SergeiBaklan again!

       

      Thanks so much for your help last time, I am now wanting it to only bring through description etc from worksheet B to worksheet A if in worksheet B the Qty is above 0 and if the Type column has machine under it. Is there a way to add this to the formula? I have added workbook with updated work sheet B.

       

      Thanks Aleisha 🙂

    • Aleisha2125's avatar
      Aleisha2125
      Copper Contributor
      Aug 22, 2019

      Thanks SergeiBaklan !!

       

      This work perfectly 🙂

Resources