Forum Discussion

Rob Flynn's avatar
Rob Flynn
Copper Contributor
Jul 05, 2018

Moving data from one sheet in Excel to a summary sheet if greater than "0"

I'm wanting data from say A1 to say F1 to populate in a summary sheet if the values entered for the Quantity cells are greater than 0.  I did a code in VB and found another code so that it updates when the workbook is opened. 

What I want to do is have the summary sheet populated when the user hits 'enter' when entering in a Quantity greater than zero.  Is it possible to do that automatically, or does the worksheet have to be closed and reopened to allow the macro to run?  

I don't want the users running the macro if at all possible.   

Thank you

Macros & VBA
Need Help

8 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jul 07, 2018

    Hey Rob-

     

    Hope your doing well.  You don't need to use VBA to get your data to populate on the Summary sheet.  You can use an array formula instead (See attached excel file for reference):

     

    =IFERROR(INDEX('Stock Items'!$A$3:$F$135, SMALL(IF('Stock Items'!$D$3:$D$135>0, MATCH(ROW('Stock Items'!$A$3:$F$135), ROW('Stock Items'!$A$3:$F$135)), ""), ROWS(A2:$A$2)), COLUMNS($A$1:A1)),"")

     

    Confirm the formula with CTRL + SHIFT + ENTER

     

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jul 09, 2018

        You're welcome.  Glad you were able to get it working!  Array formulas are quite useful in certain situations.  Here's a link to some Guidelines and examples:

         

        https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Resources