LISTS

[Guest]

Hi,

Hoping someone can help please! i have lists of loads that have been
delivered in col A, in col B i have a look up formula to record the invoice
that we piad for the load on, if not then the formula returns an X. What i
would like to be able to do is get a formula that can list the loads that
have an X beside them without having lots of blank lines, so i can see what
loads we still have outstanding to be paid?
I have the same set up on 5 sheets each for different suppliers...

Is this possible?

Thanks!

 

[Guest]

1. Easiest way is to use autofilter on col B (filter out "X"), then just copy
the filtered results n paste special as values on another sheet.

2. If you want it automated in another sheet using non-array formulas ..

Assume source data in Sheet1, cols A and B, with data in row2 down
(Key col = col B, the lines marked "X" by your formula)

In Sheet2,

Put in A2:
=IF(Sheet1!B2="X",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))

Copy B2 to C2. Select A2:C2, copy down to the max expected extent of data in
Sheet1. Hide away col A. Cols B & C will return only the lines with an "X"
from Sheet1, all neatly bunched at the top.

 

[Guest]

Thanks!

1. Easiest way is to use autofilter on col B (filter out "X"), then just copy
the filtered results n paste special as values on another sheet.

2. If you want it automated in another sheet using non-array formulas ..

Assume source data in Sheet1, cols A and B, with data in row2 down
(Key col = col B, the lines marked "X" by your formula)

In Sheet2,

Put in A2:
=IF(Sheet1!B2="X",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))

Copy B2 to C2. Select A2:C2, copy down to the max expected extent of data in
Sheet1. Hide away col A. Cols B & C will return only the lines with an "X"
from Sheet1, all neatly bunched at the top.

 

[Max]

welcome !