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

V

Vickynh

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!
 
T

Teethless mama

"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))),"",INDEX(Name,SMALL(IF((Value>=5000)*(Value<10000),ROW(INDIRECT("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy to another worksheet IF 3
need help mutiple worksheets to create monthly report sheet 1
How to compare a row data from one worksheet to another worksheet 5
Moving data from one worksheet to another 0
Sort one coloumn according to another coloumn on another worksheet 0
Sort a coloum on one worksheet according to another coloumn on ano 0
How do I copy specific information from a master sheet? 1
How do I do a VLOOKUP from 2 ranges and add the results together? 3

Top