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 wh...
Macros & VBA
Need Help
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

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

Matt,

I've tried the formula in a new worksheet, but it doesn't seem to function.  The worksheet headers are in the same place, the name of the source data ws is the same except it needs to got all the way to F9654, but nothing is populating.  Verified the formula with ctl/shift/enter, but still nothing.  Or do I also need to insert the VB code? 

  • Rob Flynn's avatar
    Rob Flynn
    Copper Contributor
    Jul 11, 2018

    OK, sorry, nevermind Matt.  It still required the VB code I discovered.  

    • Rob Flynn's avatar
      Rob Flynn
      Copper Contributor
      Jul 11, 2018

      No, actually it didn't.  It populated the summary sheet, but it looks like it has to run with any change.  

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 14, 2018

        As variant it could be done with Power Query

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    OnlyWithOrders = Table.SelectRows(Source, each ([#"Order/Count"] <> null))
in
    OnlyWithOrders

        with refresh on opening workbook and every few minutes (plus Refresh All of course).

Resources