Forum Discussion
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
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
Awesome!
Thank you Matt!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:
