Comparing Named ranges apologies for the dodgey post below

[Sam Crump]

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!

 

[Arvi Laanemets]

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

 

[Guest]

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

 

[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]

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

 

[Bob Phillips]

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)