Name List Formula Help

J

John

I have a list of names in cells A2:A30 and in cells B2:B30 I need to enter £
for example money paid each week. I need on a seperate worksheet (sheet2) a
list of names of those who have not paid or where cells B2:B30 are blank..! I
am using Excel 2003 sp3.

Any help would be very much appreciated.

Thanks,

John
 
J

John

Gary,

Thanks for the response, what you suggested works but is there anyway I can
remove the blank rows in-between the returned cells from sheet 1 in sheet 2.

Thanks,

john
 
M

Max

You can achieve the desired extracts with this relatively simple non-array
set-up

Source data is within A2:B30 in Sheet1,
names listed in A2:A30, figures (payment) to be entered in B2:B30

In Sheet2,
Put in A2: =IF(Sheet1!B2="",ROW(),"")
Leave A1 empty. This is the criteria col.
(You can easily adapt it to suit criteria for other extract scenarios)

Put in B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Copy A2:B2 down to B30. Hide/minimize col A. Col B returns the required list
of names (those with blanks in col B, ie yet to pay), with all results neatly
packed at the top.

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
J

John

Max,

That works fantastically well, just what I was looking for.

Many thanks,

John
 
Top