Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy graph from a Worksheet based on a field in another Worksheet | Excel Worksheet Functions | |||
Copy Row to worksheet based on criteria | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy Color Formats Based On Column Date Values | Excel Discussion (Misc queries) | |||
If statement...copy values of cells on another worksheet. | Excel Discussion (Misc queries) |