copy cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to copy certain cells from one worksheet to another [within same
workbook], based on mulpiple conditions. e.g. isblank($A30), $C30>0,
$B30="OPEN" etc.
Can anyone help, please.
Thanks
 
One way ..

Assume source data is in Sheet1, cols A to E, data from row30 to 200 (say),
where your joint "AND" criteria is for example: isblank($A30), $C30>0,
$B30="OPEN"

In another sheet, suppose you want to "filter over" lines in Sheet1's cols B
to E where the criteria is satisfied

Place in A1:
=IF(AND(ISBLANK(Sheet1!$A30),Sheet1!$C30>0,Sheet1!$B30="OPEN"),ROWS($1:1),"")

In B1
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!B$30:B$200,SMALL($A:$A,ROWS($1:1))))
Copy B1 across to E1. Select A1:E1, copy down to E170. Hide away col A. Cols
B to E will return the required results, neatly bunched at the top.
 
Use the IF function (and the AND or OR functions depending on how you wish
to combine the conditions).

Excel help will tell you the syntax of the functions and give examples.

If you want to work between worksheets, just start your formula by clicking
in the destination cell and typing the =sign as usual, then if at some stage
you need to refer to a cell on another book, just go to that sheet and click
in that cell, and its reference will be included in your open formula.
 
Back
Top