Comparing Named ranges apologies for the dodgey post below

I have two named ranges. One contains a list of contacts the other contains the date they returned a questionnaire or a blank if they havent,


Name

Joe Blogs
Alan Simple
Billy Carntdo Excel
Miffy The Cat


Questionnaire Returned

02/02/06


03/02/06

I'd like to automatically list, without spaces, all the contacts who are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo from example above).
Is there anyway of doing this, I've been fiddling with CSE formulas and named range intercepts all morning please help!

Hi

Instead 2 separate lists, create a table (in my example, on sheet Sheet1)

NotReturned Name ReturnDate
Joe Blogs 02/02/06
Alan Simple
Billy Carntdo Excel
Miffy The Cat 03/02/06

Into first column, enter the formula (example for A2, copy it down)
=IF(OR($B2="",$C2<>""),"",COUNTA($C$2:$C2))
Hide column A

Define named ranges
Names = OFFSET(Sheet1!$B$1,1,,COUNTA(Sheet1!$B:$B)-1,1)
(this is instead of the one you had defined, you can use it whenever you
need it - I can't see any reason you need another one)
ReturnTbl== OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$B:$B)-1,3)
(this will be your lookup table)

On another sheet, create a list of not returned books/records/whatever
Name

Into cell A2, enter the formula
=IF(ISERROR(VLOOKUP(ROW()-1,ReturnTbl,2,0)),"",VLOOKUP(ROW()-1,ReturnTbl,2,0
))
and copy it down.
There you are!


Arvi Laanemets



"Sam Crump" <[email protected]> wrote
in message news:[email protected]...
>
> I have two named ranges. One contains a list of contacts the other
> contains the date they returned a questionnaire or a blank if they
> havent,
>
>
> Name
>
> Joe Blogs
> Alan Simple
> Billy Carntdo Excel
> Miffy The Cat
>
>
> Questionnaire Returned
>
> 02/02/06
>
>
> 03/02/06
>
> I'd like to automatically list, without spaces, all the contacts who
> are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo
> from example above).
> Is there anyway of doing this, I've been fiddling with CSE formulas and
> named range intercepts all morning please help!
>
>
> --
> Sam Crump
> ------------------------------------------------------------------------
> Sam Crump's Profile:
http://www.excelforum.com/member.php...o&userid=32184
> View this thread: http://www.excelforum.com/showthread...hreadid=519388
>

This appears to be the same queston under a different Title as the one you
posted a couple of hours ago. Did you look at my response to that post? If
it did not work for you, tell us why and perhaps we can suggest a better
solution.



Vaya con Dios,
Chuck, CABGx3


"Sam Crump" wrote:

>
> I have two named ranges. One contains a list of contacts the other
> contains the date they returned a questionnaire or a blank if they
> havent,
>
>
> Name
>
> Joe Blogs
> Alan Simple
> Billy Carntdo Excel
> Miffy The Cat
>
>
> Questionnaire Returned
>
> 02/02/06
>
>
> 03/02/06
>
> I'd like to automatically list, without spaces, all the contacts who
> are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo
> from example above).
> Is there anyway of doing this, I've been fiddling with CSE formulas and
> named range intercepts all morning please help!
>
>
> --
> Sam Crump
> ------------------------------------------------------------------------
> Sam Crump's Profile: http://www.excelforum.com/member.php...o&userid=32184
> View this thread: http://www.excelforum.com/showthread...hreadid=519388
>
>

Select a group of cells for the results and enter this formula in the
formula bar

=IF(ISERROR(SMALL(IF((Dates="")*(Names<>""),ROW(Names),""),ROW(Names))),"",
INDEX(Names,SMALL(IF(Dates="",ROW(Names),""),ROW(Names))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Make sure the names referto an absolute range, especially row absolute.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sam Crump" <[email protected]> wrote
in message news:[email protected]...
>
> I have two named ranges. One contains a list of contacts the other
> contains the date they returned a questionnaire or a blank if they
> havent,
>
>
> Name
>
> Joe Blogs
> Alan Simple
> Billy Carntdo Excel
> Miffy The Cat
>
>
> Questionnaire Returned
>
> 02/02/06
>
>
> 03/02/06
>
> I'd like to automatically list, without spaces, all the contacts who
> are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo
> from example above).
> Is there anyway of doing this, I've been fiddling with CSE formulas and
> named range intercepts all morning please help!
>
>
> --
> Sam Crump
> ------------------------------------------------------------------------
> Sam Crump's Profile:
http://www.excelforum.com/member.php...o&userid=32184
> View this thread: http://www.excelforum.com/showthread...hreadid=519388
>

Many many thanks to all those who've provided help with my query,

CLR - Yes I have posted twice. I got in a flap after the first one and decided it was best to try again. Apologies but I can't find your reply now!

Bob - I'm having a go at making you CSE formula work but so far I'm only getting the last name on the list, who has a date by their name already. Does it matter that my two named ranges are columns in the same table? what else am i doing wrong?

I've entered the following formula in a column shaped array:

{=IF(ISERROR(SMALL(IF((RegionReturn="")*(RegionName<>""),ROW(RegionName),""),ROW(RegionName))),"",INDEX(RegionName,SMALL(IF(RegionReturn="",ROW(RegionName),""),ROW(RegionName))))}


All the names refer to absolute cell references and all the arrays are of the same dimensions (although the majority of both are empty cells)

Sam

Originally Posted by Bob Phillips

Select a group of cells for the results and enter this formula in the
formula bar

=IF(ISERROR(SMALL(IF((Dates="")*(Names<>""),ROW(Names),""),ROW(Names))),"",
INDEX(Names,SMALL(IF(Dates="",ROW(Names),""),ROW(Names))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Make sure the names referto an absolute range, especially row absolute.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sam Crump" <[email protected]> wrote
in message news:[email protected]...
>
> I have two named ranges. One contains a list of contacts the other
> contains the date they returned a questionnaire or a blank if they
> havent,
>
>
> Name
>
> Joe Blogs
> Alan Simple
> Billy Carntdo Excel
> Miffy The Cat
>
>
> Questionnaire Returned
>
> 02/02/06
>
>
> 03/02/06
>
> I'd like to automatically list, without spaces, all the contacts who
> are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo
> from example above).
> Is there anyway of doing this, I've been fiddling with CSE formulas and
> named range intercepts all morning please help!
>
>
> --
> Sam Crump
> ------------------------------------------------------------------------
> Sam Crump's Profile:
http://www.excelforum.com/member.php...o&userid=32184
> View this thread: http://www.excelforum.com/showthread...hreadid=519388
>

Sam,

I guess the problem is because the range RegionName is not in rows 1,2,3,
etc.

Try this variation

=IF(ISERROR(SMALL(IF((RegionReturn="")*(RegionName<>""),ROW(RegionName),""),
ROW(INDIRECT("1:"&ROWS(RegionName))))),"",
INDEX(RegionName,SMALL(IF(RegionReturn="",ROW(INDIRECT("1:"&ROWS(RegionName)
)),""),ROW(INDIRECT("1:"&ROWS(RegionName))))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sam Crump" <[email protected]> wrote
in message news:[email protected]...
>
> Many many thanks to all those who've provided help with my query,
>
> CLR - Yes I have posted twice. I got in a flap after the first one and
> decided it was best to try again. Apologies but I can't find your reply
> now!
>
> Bob - I'm having a go at making you CSE formula work but so far I'm
> only getting the last name on the list, who has a date by their name
> already. Does it matter that my two named ranges are columns in the
> same table? what else am i doing wrong?
>
> I've entered the following formula in a column shaped array:
>
>
{=IF(ISERROR(SMALL(IF((RegionReturn="")*(RegionName<>""),ROW(RegionName),"")
,ROW(RegionName))),"",INDEX(RegionName,SMALL(IF(RegionReturn="",ROW(RegionNa
me),""),ROW(RegionName))))}
>
>
> All the names refer to absolute cell references and all the arrays are
> of the same dimensions (although the majority of both are empty cells)
>
> Sam
>
>
>
>
>
>
>
>
>
> Bob Phillips Wrote:
> > Select a group of cells for the results and enter this formula in the
> > formula bar
> >
> >
=IF(ISERROR(SMALL(IF((Dates="")*(Names<>""),ROW(Names),""),ROW(Names))),"",
> > INDEX(Names,SMALL(IF(Dates="",ROW(Names),""),ROW(Names))))
> >
> > which is an array formula, it should be committed with
> > Ctrl-Shift-Enter, not
> > just Enter.
> >
> > Make sure the names referto an absolute range, especially row
> > absolute.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Sam Crump" <[email protected]>
> > wrote
> > in message
> > news:[email protected]...
> > >
> > > I have two named ranges. One contains a list of contacts the other
> > > contains the date they returned a questionnaire or a blank if they
> > > havent,
> > >
> > >
> > > Name
> > >
> > > Joe Blogs
> > > Alan Simple
> > > Billy Carntdo Excel
> > > Miffy The Cat
> > >
> > >
> > > Questionnaire Returned
> > >
> > > 02/02/06
> > >
> > >
> > > 03/02/06
> > >
> > > I'd like to automatically list, without spaces, all the contacts who
> > > are yet to return questionnaires (i.e. Alan Simple and Billy Carntdo
> > > from example above).
> > > Is there anyway of doing this, I've been fiddling with CSE formulas
> > and
> > > named range intercepts all morning please help!
> > >
> > >
> > > --
> > > Sam Crump
> > >
> > ------------------------------------------------------------------------
> > > Sam Crump's Profile:
> > http://www.excelforum.com/member.php...o&userid=32184
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=519388
> > >
>
>
> --
> Sam Crump
> ------------------------------------------------------------------------
> Sam Crump's Profile:
http://www.excelforum.com/member.php...o&userid=32184
> View this thread: http://www.excelforum.com/showthread...hreadid=519388
>