One simple play to get it up & away w/o fuss

Source data assumed in Sheet1, cols A to C, from row2 down
In another sheet,
In A2: =IF(COUNT(Sheet1!B2:C2)=2,ROW(),"")
Leave A1 blank. This is the criteria col.

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
data in Sheet1. Minimize/hide col A. Cols B to D will return the required
results from Sheet1's cols A to C, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"joet7" wrote:
This is my query put simply, the actual spreadsheet I want to use this on is
huge.

I have 3 columns
A - ref number
B - units requested from store a
C - units requested from store b

What I want is a formula which I can enter on a second sheet of the same
workbook which will display the reference number and units requested of only
those rows which have a value in column B, C or both, but ignore the blanks
(ie when no units have been requested). The orginal 'raw data' must remain
intact.

Hope this makes sense, any ideas?