Show a row of data in a different sheet if cell isn't null

O

OCDinNC

If a cell is not blank or null (meaning I have used some of those items), I
need the row of data to copy to the next worksheet. How can I do this?
 
M

Max

One quick way to set it up dynamically

Assume source data in Sheet1, cols A to C, where the key col = col A
In another sheet,
In A2: =IF(Sheet1!A2 said:
If a cell is not blank or null ...
Click to expand...

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
source data in Sheet1, say down to D100. Minimize col A. Cols B to D will
return the desired results from Sheet1, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
 
O

OCDinNC

Thanks so much for your reply, Max. I am pretty new to using Excel, and am
trying to fit your example to my situation. The range I need to filter is H,
so cells H2 through H145 on sheet 2 need to be scanned for the presence of a
number, and if there is one, then copy the rows containing numbers to sheet
1. Can you elaborate? Thanks!
 
M

Max

So your source data is in Sheet2, cols A to col H (say)
with key col = col H, data from row 2 down

Assume you want to scan col H for the number: 77 (say) - the criteria

In Sheet1,
In A2: =IF(Sheet2!H2=77,ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to I2. Select A2:I2, copy down to cover the max expected extent of
source data in Sheet2, say down to I200? Minimize col A. Cols B to I will
return the desired results from Sheet2, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
M

Max

I've responded further with the amended steps to suit your specific set-ups
in the other branch. You might wish to try that instead. It should work fine.
Pl press the YES buttons below from where you're reading this.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
Top