using lookup to copy more than one cell

[DanFox]

I have a spreadsheet:

Worksheet 2
A B C D
1 Date Item Price Total
2 1st Apr Widget £1 £3
3 2nd Apr Brush £2 £2
4 3rd Apr Brush £2 £0
5 4th Apr Widget £1 £5


I would like to create a formula in worksheet 1 that returns the data
held in columns A, B, C and D of worksheet 2 - but only where the
value in D is greater than 0.

If the formula works, worksheet 1 should appear as:

A B C D
1 Date Item Price Total
2 1st Apr Widget £1 £3
3 2nd Apr Brush £2 £2
4 4th Apr Widget £1 £5


I know I need some kind of lookup statement, but can someone help by telling me what the formula needs to be?

I don't fully understand lookup formula, particularly in cases such as
this, where i need to copy data from more than one cell.

 

[Max]

Maybe try this ..

In Sheet2 (your table)
------------
Put in E2: =IF(OR(D2=0,D2=""),"",ROW())
Copy E2 down

In Sheet1
-------------
Assume identical col headers are in A11
as that in Sheet2

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet2!$E:$E,ROW(A1)),Sheet2!$E:$E,0)),"",OFFSET(She
et2!$A$1,MATCH(SMALL(Sheet2!$E:$E,ROW(A1)),Sheet2!$E:$E,0)-1,COLUMN(A1)-1))

Copy A2 across to D2, then fill down
as many rows as there is data in Sheet2

Format as necessary,
e.g.: cols C and D as currency, col A as date?

The above should return what you're after in Sheet1
(i.e. rows with Total = 0 in Sheet2 will not appear)
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

I have a spreadsheet:

telling me what the formula needs to be?