IF with PIVOT (I want to copy some of the table to the other sheet in excel)

Hi there,

I am new in here so I am sorry for any mistake.

I have table in an excel spreadsheet that I would like to take some informations to the other sheet.

I am not sure if I could upload the sample file ,so I did summarize my excel table above. IF I could manage the upload , please take a look at it

In the table;
Bcoloumn is "Country" H "products interested"
C is "adress" I "notes"
D is "firm" J "action"
E is "telephone number"
F is "e-mail"
G "scope of activity" ,


SO here the question.. I would like to take "country" "firm" "e-mail" sections to the other sheet IF ACTION PART IS "MAIL SENT" !!

And I am assuming that I could solve this with Pivot table so thats why the title is Pivot .

Many thanks!!

There are a few ways to achieve the table you want
Here is a solution that uses Advanced Filter
Note that I inserted 1 row in the source sheet to create a gap between the table and the "Criteria Range"

1.Create the "Criteria Range" in the source sheet - note the heading must be IDENTICAL to main table

AdvFilterSource.jpg

2. Go to Destination sheet and create the headings you want - note the headings must be EXACTLY the same as in main table - so perhaps use a formula to put the values there

Formula:

Please Login or Register  to view this content.

AdvFilterDestination.jpg

3. Move cursor away from headings to cell D8 perhaps

4. Click on Data tab and Advanced Filter
(CURSOR MUST BE IN THE DESTINATION SHEET WHEN YOU CLICK ON ADVANCED FILTER)
- select "Copy to another location"
- set up the ranges as per image
- note that the "List Range" is the whole table including headings

AdvFilterRanges.jpg

5. Click "OK"

AdvFilterResult.jpg

Perhaps you did not select the List Range correctly

AdvFilt01.jpg


AdvFilt02.jpg

Attached is file containing a macro to create it automatically
Open the file, enable macros and run the macro with {CTRL} t

Please Login or Register  to view this content.

Originally Posted by kev_

Perhaps you did not select the List Range correctly

Attachment 508550


Attachment 508552

Thank you for your support very much! I understand it now. It helped a lot and I learn new things thanks to your explanations.

Do you think there is a way if I change one action from f.e "replied" to "mail sent" the sheet in 2nd page automatically changes? or is it a whole different topic that I shouldn't write here.

And if I change criteria range from "mail sent" to another criteria, does the 2nd sheet automatically change or do I need to make another advance filter from the beginning ?

Have a look at the file I attached in post#7
I put a dropdown in the J2

Change the value and run the macro again

I have updated the file so that it automatically runs the macro when you change the selection in J2
Download and Open the attached file, enable macros, and change the selected value in J2


This was added in the sheet module

Please Login or Register  to view this content.

(right-click on sheet tab Sayfa1 and select "view code")