Forum Discussion
VBA Code to Automatically Copy and Paste a Range of Data if the Criteria is Met
=IFERROR(INDEX(Sheet2!D$81:D$86,SMALL(IF(Sheet2!$F$81:$F$86>Sheet2!$G$81:$G$86,ROW(Sheet2!$A$81:$A$86)-80),ROW($A1))),"")Without VBA you can try this formula. The formula has to be entered as an arrayformula with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. In the example the formula is in cell H2 of sheet8 and filled across range H2:K8.
Sheet2:
Sheet8:
Hi mathetes ,
Unfortunately, I have an older version of excel and plan on sending the file to someone who has an older version as well. I tried a good old INDEX/MATCH/ROWS function to accommodate for not having the FILTER function and it didn’t work either it came up saying “#REF!” or “NA” or “VALUE” or only pastes a value from one cell.
=IFERROR(INDEX(Sheet2!D$81:D$86,SMALL(IF(Sheet2!$F$81:$F$86>Sheet2!$G$81:$G$86,ROW(Sheet2!$A$81:$A$86)-80),ROW($A1))),"")Without VBA you can try this formula. The formula has to be entered as an arrayformula with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. In the example the formula is in cell H2 of sheet8 and filled across range H2:K8.
Sheet2:
Sheet8:
- I am trying to modify the function to apply it to something else. In this case, I am trying to copy and paste the range of cells were the value is greater than “>0”. Unfortunately, in each modification of the function, it isn’t working like the recommendation.
One column contains a list and in another column is a SUMIF function that corresponds to the list column. I’m trying to use the function to only copy the values in that range where the SUMIF column has values “>0” and paste them on another sheet.
I’ve tried looking to see if other people have posted a similar question and gotten an answer but haven’t found anything replicable.
Again, I am open to a code that could automatically run as new data is added.Could you attach a screenshot of your data (without sensitive information) and of the expected result?
Sure! So I've designed a simplified mockup and attached it for you. Hope it makes sense. But I'll explain it a bit more below.
On one sheet (named"Sidework") I have tons of data which is where my drop-down lists are derived from.
I then used another sheet named "Sheet6" to house data. The first column is a direct reference to the list from the "Sidework" sheet so that they are updated simultaneously. The second column contains the SUMIF formula
My summary sheet, "Sheet7" is where the values and data are updated. So for each person, I can select the item from the drop-drown list created, and insert how much they've earned from it. (This is what my SUMIF function is referencing.)
This is where I need help:
At another location on the same sheet, I want to display the data in another way. Showing the total earned per item rather than per person. But, I want excel to filter the data from Sheet6 and just copy and paste the range of data (just values and format) where the values in column B are greater than 0.So I tried to modify the function you gave me, by applying it to the data on Sheet6. But, I haven't had success (I think it's because the entire data set is technically a function.) Similar to the first example, my data is dynamic, so I need something that works with changing data.
*Note: I'm open to all suggestions as well. Thank you
