Excel 2003 - I Need To Copy the Data Identified By SUMPRODUCT To Another Sheet? Hi There.

Please could someone point me in the right direction to solve a problem I'm having?

In my workbook Sheet 1 contains a dynamic range from A1 to J880. (Capacity is 65536).

Sheet 2 has different SUMPRODUCT formulae to count the occurrences in the range which fulfil certain criteria, sometimes with as many as 4 conditions.

SUMPRODUCT returns an accurate number of occurrences but I need a function/macro/formula to take things a step further.

Is there a way (without autofilter or pivot table) to automatically copy all the data in the rows identified in the range by the SUMPRODUCT formula into a separate list on Sheet 2?

I know how to sort and autofilter the data manually but I'm designing the workbook to simplify and automate the process as much as possible for people who hate 'having to use' computers (strange people that they are!).

Many Thanks. Gordon.

I don't know what you mean by "the rows identified in the range by the SUMPRODUCT formula". Please explain further.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Oops sorry, please forgive my inexperience with Excel, I know what I mean and assume every one else does too. lol. Many thanks for replying DerbyDad03.

From the 880 rows of data in the range, let's say 7 are counted by the SUMPRODUCT function as satisfying the criteria specified in the formula.

The number '7' is returned to the same cell in which the SUMPRODUCT function is found in Sheet 2.

Is there a way at all for excel to then copy those 7 rows only into a list on Sheet 2 below the cell which displays the number 7?

I appreciate that my knowledge of the application (whilst increasing slowly) is very limited and there may be a far better method to examine the rows I need from the main range and copy them. It's just that it was quite a personal achievement to formulate the SUMPRODUCT function to accurately count the specified rows, now I need those rows to be copied into a new list on a different sheet of the same workbook.

I hope this explains my query, thank you for considering the question.

I'm obliged.Gordon.

It's possible that you could use VLOOKUP to pull the data into the new sheet but without knowing exactly what you are comparing I obviously can't offer any formula suggestions. It may require the use of VBA which, while very powerful, can sometimes be problematic when used in workbooks that are shared by others. For one thing, macros have to be enabled on their systems, which can not be done automatically for security reasons.

Again, without knowing exactly what you are counting and how it is being done I can't offer any specific advice.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi, Do you think it would it be best for me to upload a sample of the document with some more explanation?

Thanks, Gordon.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History