Extending Logic beyond the numbers and into a Name! Is it Possible

G

Guest

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan
 
G

Guest

try:

=IF(ISERROR(SMALL(IF(('New Rule Clients'!$Y$4:$Y$3500="Yes")*('New Rule
Clients'!$Z$4:$Z$3500=""),ROW('New Rule
Clients'!$Y$4:$Y$3500),""),ROW($A1))-ROW($A$4)+1),"",INDEX('New Rule
Clients'!A$4:A$3500,N(SMALL(IF(('New Rule Clients'!$Y$4:$Y$3500="Yes")*('New
Rule Clients'!$Z$4:$Z$3500=""),ROW('New Rule
Clients'!$Y$4:$Y$3500),""),ROW($A1))-ROW($A$4)+1)))
Entered with Ctrl+Shift+Enter
 
G

Guest

On limited testing I did, it worked.

What/why didn't it take?

Did you enter with Ctrl+Shift +Enter?

Can you send sample data as it's difficult to test in isolation?

toppers <at> nospamjohntopley.fsnet.co.uk

remove nospam
 
G

Guest

I spoke to soon Toppers. I did get it to work. The sole problem was that only
the first of the 11 names (the one identified in cell A4) which meet the
criteira of the formula (YES in Row Y, and NO DATE in Row Z) appear. Because
I know that there are 11 folk who meet the criteria, I was hoping the formula
outcome would generate all of their names? I didn't know if this is too
complicated a task for Excel to handle.

Dan
 
G

Guest

Copy the formula down until you get a blank: I didn't make this clear. It
should display all names in concurrent rows.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Suggestion Please? 4
Help with an Excel formula please. - it is beyond me!! 2
I have a feeling the order of my formula is out of order....HELP! 1
How do I pick the only number in a column and copy it to another? 3
is it possible to enter in text over a formula without deleting the formula? 3
Is it possible to retrieve the variable name of a property ? 1
Group and calculate in a querie, is it possible? 5
Understanding the Cell Microprocessor [VERY LONG] 12

Top