New at Excel and need help on a worksheet

[Jennifer]

I need to create a worksheet that will have several names in a column. Next
to the names I will have three more columns that are requirements or goals
that these people need to meet. When all three goals are met i then need the
worksheet to create a new list with those names and if at all possible print
out name tags with the persons name on it.

Example:
Current Test Ran 5 miles 20 pushups
John Doe x x
x
Mary Smith x
x
Pam Brown x x
x

So after I enter all the information I want it to find the names that have
met all three requirements and create a list that would say

John Doe
Pam Brown
Then I have a program that integrates with excel its Avery label maker, and
I would like to import those names into there and create name tags. Is there
anyway to do this?
Thank you so much...

 

[Gord Dibben]

In column E enter =IF(COUNTA(B11)=3,"complete","not")

Autofilter on Column E to get a list of completed names for your import.


Gord Dibben MS Excel MVP

 

[Jennifer]

Thank you.. that got me where i want to be but I was wondering if instead of
saying complete I could just have their name show up there.. and if its not
complete just leave it blank? Thank you very much

 

[Max]

Try this set-up, it should deliver what you seek ..

Source data as posted assumed in cols A to D, data from row2 down
In E2: =IF(COUNTIF(B22,"x")=3,ROW(),"")
Leave E1 empty. This is the criteria col.

In F2: =IF(ROWS($1:1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROWS($1:1))))
Copy E2:F2 down to cover the max expected extent of data. Col F will return
the desired dynamic list of names which meet all 3 requirements in cols B to
D (marked by "x"), with all names neatly packed at the top. Minimize/hide col
E if necess.

Celebrate success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Bernard Liengme]

=IF(COUNTA(B11)=3,A1,"")
This will display the name (when there are 3 x's) or nothing (when number of
x's is less than 3)

To print name tags use Mail Merge feature in Word having it take the data
from your Excel file. To use Microsoft Word to do the printing with Excel as
the database see -
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
best wishes

 

[Gord Dibben]

Can be done.............see Bernie's post.

But if you use my formula and autofilter, the names you want will be in
Column A without any blank rows showing.


Gord

 

[Jennifer]

Max,

It worked great, but I have found a change I need to make. I am going to
put the first name in column A and last in Column B, how would I add that to
the formula?
One other thing.. if in those columns we wanted to put something other than
a "X" such as a date or comment, how would you do that? Thanks again for
your help

 

[Max]

Max, It worked great ..

That's good

.. but I have found a change I need to make.
I am going to put the first name in column A and last in Column B,
how would I add that to the formula?

Ah. Guess you mean how to extend the earlier set-up
to now extract both name cols A and B?

In F2: =IF(COUNTIF(C2:E2,"x")=3,ROW(),"")
Leave F1 empty

In G2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(A:A,SMALL($F:$F,ROWS($1:1))))
Copy G2 to H2. Select F2:H2, copy down to cover the max expected extent of
data. Col F will return the desired dynamic list of names which meet all 3
requirements in cols C to E (marked by "x"), with all names neatly packed at
the top. Minimize/hide col F if necess.

Col G is essentially the same extract expression as in col F earlier, except
that the point to the (new) criteria col F now needs to be fixed with the $
signs, for copying across purposes. The INDEX(A:A .. part which returns the
required results is left relative so that it becomes INDEX(B:B .. in col H.

One other thing.. if in those columns we wanted to put something other than
a "X" such as a date or comment, how would you do that?

Presuming that these entries (whatever, could be dates [ie nums] or text)
would carry the same implications as the earlier "x" markings, just use
COUNTA to replace COUNTIF in the new criteria col F:

In F2: =IF(COUNTA(C2:E2)=3,ROW(),"")

High-five? Click YES below

P/s: In general, you should post new queries afresh as new threads.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---