Forum Discussion

purkeenjee's avatar
purkeenjee
Copper Contributor
Oct 15, 2020

Copy cells from one sheet to another

Hi guys,

 

I'm making an excel project for my work regarding inventory of items. I wanted to know how to copy certain rows/cells from one sheet to another sheet.

 

Sheet 1 provides all the items and each item is classified as "Good" or "Re-order"

I want all items classified as "Re-order" to automatically be copied to Sheet 2

 

SHEET 1:

 

SHEET 2: This is what I like to happen, it gives all the items with codes from the other sheet that needs to be re-ordered

 

Any suggestion and help are greatly appreciated. THank you!

excel
Formulas and Functions

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Oct 18, 2020

    purkeenjee 

     

    =IF(ROW(Tabelle1!1:1)>COUNTIF(Tabelle1!$B$2:$B$20;">0");"";INDEX(Tabelle1!A:A;SMALL(IF(Tabelle1!$B$2:$B$20>0;ROW(Tabelle1!$2:$20));ROW(A1))))

    Caution, matrix formula included!
    The curly braces {} are not entered.
    Exit the cell editor with Ctrl + Shift + Enter instead of Enter alone.

     

    array examble

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 15, 2020

    purkeenjee 

    Which Excel version you are using? Do you have access to the dynamic array formulas?

    There are two ways you can achieve this, Formulas and VBA, which way would you prefer?

    Can you upload a sample workbook instead of an image to work with?

    • purkeenjee's avatar
      purkeenjee
      Copper Contributor
      Oct 18, 2020

      Subodh_Tiwari_sktneer 

       

      Thanks for your response. I've uploaded the excel file for you to check. I'm using Excel 2010, I know it's kind of outdated but it's the version we have in the hospital. I prefer formula rather than VBA as I'm not really trained for the latter.

       

      Thanks again for your help. Any suggestion is appreciated 🙂

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Oct 18, 2020

        purkeenjee 

        Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

         

        On Ordering Sheet

        In A5

        =IFERROR(INDEX(ITEMS!B$5:B$38,SMALL(IF(ITEMS!$A$5:$A$38="Re-Order",ROW($A$5:$A$38)-ROW($A$5)+1),ROWS(A$5:A5))),"")

        Confirm with Ctrl+Shift+Enter and then copy it across to next column and down the rows.

         

        Or you may try this Regular Function which doesn't require any special keystroke.

        In A

        =IFERROR(INDEX(ITEMS!B$5:B$38,SMALL(IF(ITEMS!$A$5:$A$38="Re-Order",ROW($A$5:$A$38)-ROW($A$5)+1),ROWS(A$5:A5))),"")

        and then copy it across to next column and down the rows.

         

        The attached file contains both the formulas.

         

Resources