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?

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?