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

In Sheet2, enter:

=IF(Sheet1!B2=0,Sheet1!A2,"") and copy down
--
Gary''s Student - gsnu200855


"John" wrote:

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

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

"Gary''s Student" wrote:

In Sheet2, enter:

=IF(Sheet1!B2=0,Sheet1!A2,"") and copy down
--
Gary''s Student - gsnu200855


"John" wrote:

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

Yes, but the formula is a lot more complex. See:

http://office.microsoft.com/en-us/ex...260381033.aspx

Update this post if you still require help.
--
Gary''s Student - gsnu200855


"John" wrote:

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

"Gary''s Student" wrote:

In Sheet2, enter:

=IF(Sheet1!B2=0,Sheet1!A2,"") and copy down
--
Gary''s Student - gsnu200855


"John" wrote:

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

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,SMAL L(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
---
"John" wrote:
is there anyway I can remove the blank rows in-between
the returned cells from sheet 1 in sheet 2.

Max,

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

Many thanks,

John

"Max" wrote:

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,SMAL L(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
---
"John" wrote:
is there anyway I can remove the blank rows in-between
the returned cells from sheet 1 in sheet 2.

Glad to hear that, John.
Could you kindly click the YES button
in that response (like the ones below)?
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"John" wrote:
Max,
That works fantastically well, just what I was looking for.
Many thanks,

John