Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cells based on a criterion
Here is my problem.
I have cells A1:a100 full of numbers. I wish to select all numbers that are lower than 20 for example and paste them into cel B1 and down. I know i could copy and paste but I need more complicated calculations than that. THEREFORE is it possible to copy cells based on af statements etc?? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cells based on a criterion
Hi Chris
Try this If you filter with EasyFilter "Less than 20" and choose "Copy Next to your data" you get what you want. http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Chris_t_2k5" wrote in message ... Here is my problem. I have cells A1:a100 full of numbers. I wish to select all numbers that are lower than 20 for example and paste them into cel B1 and down. I know i could copy and paste but I need more complicated calculations than that. THEREFORE is it possible to copy cells based on af statements etc?? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cells based on a criterion
Just another option to play with, using non-array formulas
Put in B1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) Put in C1: =IF(A1="","",IF(A1<20,ROW(),"")) Select B1:C1, copy down to C100 Col B auto-returns the required results, all neatly bunched at the top Col C is the criteria col. Perhaps better than hardcoding the "20" in the criteria, for flexibility, we could simply point to a cell, say D1 for the criteria value, i.e. use instead in C1: =IF(OR(A1="",$D$1=""),"",IF(A1<$D$1,ROW(),"")) In that way, we can easily change the criteria for the returns in col B w/o having to amend and re-fill the formulas in col C -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Chris_t_2k5" wrote in message ... Here is my problem. I have cells A1:a100 full of numbers. I wish to select all numbers that are lower than 20 for example and paste them into cel B1 and down. I know i could copy and paste but I need more complicated calculations than that. THEREFORE is it possible to copy cells based on af statements etc?? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cells based on a criterion
Thanks i'll have a go at these and let you know how i get on
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cells based on a criterion
You're welcome, Chris
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Chris_t_2k5" wrote in message ... Thanks i'll have a go at these and let you know how i get on |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
adding color to cells based on a formula | Excel Worksheet Functions | |||
Creating a conditional format for a cell based on another cell's v | Excel Discussion (Misc queries) | |||
zero value when copying cells between worksheets | Excel Discussion (Misc queries) | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions |