How do I copy cells from one worksheet to another based on values
 

I have a worksheet that looks like this:

Worksheet 1
A B C
M/M Brown Brown Address 1000
M/M Adams Adams Address 6000
M/M Jones Jones Address 5000
M/M Smith Smith Address 10000
M/M Doe Doe Address 8000
M/M Hunt Hunt Address 15000

I need to copy the values in column A and column B to a new worksheet where
column C is greater than or equal to 5000 AND column c is less than 10000.
So the result woud be that worksheet2 looks like this:
A B
M/M Adams Adams Address
M/M Jones Jones Address
M/M Doe Doe Address

Ideally this would happen whenever I added a new row to worksheet1. Just to
add a wrinkle, I am using Excel 2008 for Mac and do not have any VB marco
capabilities. Help!

How do I copy cells from one worksheet to another based on values
 

"Name" is a define name range (name range list)
"address" is a define name range (address range list)
"Value" is a define name range (Value range list)
Note: no quotes

For name:
=IF(ISERR(SMALL(IF((Value=5000)*(Value<10000),ROW (INDIRECT("1:"&ROWS(Value)))),ROWS($1:1))),"",INDE X(Name,SMALL(IF((Value=5000)*(Value<10000),ROW(IN DIRECT("1:"&ROWS(Value)))),ROWS($1:1))))

For address:
change the "Name" to "address"


ctrl+shift+enter, not just enter on PC comp.
for MAC computer I think CMD+enter

copy down as far as needed



"Vickynh" wrote:

I have a worksheet that looks like this:

Worksheet 1
A B C
M/M Brown Brown Address 1000
M/M Adams Adams Address 6000
M/M Jones Jones Address 5000
M/M Smith Smith Address 10000
M/M Doe Doe Address 8000
M/M Hunt Hunt Address 15000

I need to copy the values in column A and column B to a new worksheet where
column C is greater than or equal to 5000 AND column c is less than 10000.
So the result woud be that worksheet2 looks like this:
A B
M/M Adams Adams Address
M/M Jones Jones Address
M/M Doe Doe Address

Ideally this would happen whenever I added a new row to worksheet1. Just to
add a wrinkle, I am using Excel 2008 for Mac and do not have any VB marco
capabilities. Help!

How do I copy cells from one worksheet to another based on values
 

Hi,

You can achieve your result by using Advanced Filters. Kindly refer to the
help menu

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vickynh" wrote in message
...
I have a worksheet that looks like this:

Worksheet 1
A B C
M/M Brown Brown Address 1000
M/M Adams Adams Address 6000
M/M Jones Jones Address 5000
M/M Smith Smith Address 10000
M/M Doe Doe Address 8000
M/M Hunt Hunt Address 15000

I need to copy the values in column A and column B to a new worksheet
where
column C is greater than or equal to 5000 AND column c is less than 10000.
So the result woud be that worksheet2 looks like this:
A B
M/M Adams Adams Address
M/M Jones Jones Address
M/M Doe Doe Address

Ideally this would happen whenever I added a new row to worksheet1. Just
to
add a wrinkle, I am using Excel 2008 for Mac and do not have any VB marco
capabilities. Help!