Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy a set of cells based on a logical condition
I've names in column A and status level (low or medium or high) in column B.
I want to copy all 'names' with 'status level' as 'low' in to a different sheet through a formula. I do not want to do this manually. similarly all all 'names' with 'status level' as 'medium' in to a different sheet and so on. I also want the value to get updated automatically if I change the status in original sheet from 'low' to 'medium' |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy a set of cells based on a logical condition
Select a range of cells in your target sheet, say A1:A20, that is as big as
you will ever need, then in the formula bar add =IF(ISERROR(SMALL(IF('Sheet 1'!B1:B20="Low",ROW($B1:$B20),""),ROW($B1:$B20))), "", INDEX('Sheet 2'!A1:A20,SMALL(IF('Sheet 1'!B1:B20="Low",ROW($B1:$B20),""),ROW($B1:$B20)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "getdv" wrote in message ... I've names in column A and status level (low or medium or high) in column B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
Conditional Format based on 3 other cells. | Excel Discussion (Misc queries) | |||
copy data from 1 worksheet to another based on a condition | Excel Worksheet Functions | |||
reminder notifications in a column | Excel Discussion (Misc queries) |