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

need help mutiple worksheets to create monthly report sheet 1
How do I do a VLOOKUP from 2 ranges and add the results together? 4
Copy to another worksheet IF 3
How to compare a row data from one worksheet to another worksheet 5
How do I copy specific information from a master sheet? 1
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Need help mutiple worksheets to create a report worksheet 2
Copy data from one worksheet to another 5

Top