copy selection to another sheet

cayang002

New Member
Hi, All,

Thanks for all the help.

I have a five column data with unknown rows. I want to select certain records and copy them into another sheet, my selection cretieria are: column A="A", AND column D="B" or "C", or "#N/A" since it based on a formula. if selection creteria for column D is ""#N/A", just ignore this creteria.

Thanks!

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

texasalynn

Well-known Member
in a blank cell put this formula
=IF(AND(A1="A",OR(D1="B",D1="C")),1,0)
then copy down, filter for the 1

cayang002

New Member
Thanks for the reply. I've thought the same formula and it works fine except the "#N/A" part. the creteria for column D is come from another range, say, H1:H6, based on different input, any cell in the range can be "#N/A". What I want is if cell is "#N/A", just ignore this cell and select based on next cell in the range.

The selection creteris is as follows:

for Column A for Column D
A D
#N/A"
C

Thanks!

cayang002

New Member
Sorry, the text all mesed up.
The creteris for column A is:A
For column D is: "C", or "D", or '#N/A"

xenou

MrExcel MVP
Maybe:
=IF(OR(ISNA(D2),A2="A"),1,IF(OR(D2="C",D2="D"),1,0))

?
Alex

Replies
8
Views
449
Replies
1
Views
97
Replies
5
Views
195
Replies
5
Views
124
Replies
6
Views
291

1,196,022
Messages
6,012,908
Members
441,741
Latest member
abaz21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back