Name List Formula Help
 

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

Name List Formula Help
 

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

Name List Formula Help
 

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

Name List Formula Help
 

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

Name List Formula Help
 

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.

Name List Formula Help
 

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.

Name List Formula Help
 

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