If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New at Excel and need help on a worksheet
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... |
#2
|
|||
|
|||
New at Excel and need help on a worksheet
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 On Wed, 25 Feb 2009 15:11:01 -0800, Jennifer wrote: 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... |
#3
|
|||
|
|||
New at Excel and need help on a worksheet
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 "Gord Dibben" wrote: 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 On Wed, 25 Feb 2009 15:11:01 -0800, Jennifer wrote: 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... |
#4
|
|||
|
|||
New at Excel and need help on a worksheet
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,R OWS($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 --- "Jennifer" wrote: 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 |
#5
|
|||
|
|||
New at Excel and need help on a worksheet
=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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jennifer" wrote in message ... 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 "Gord Dibben" wrote: 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 On Wed, 25 Feb 2009 15:11:01 -0800, Jennifer wrote: 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 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... |
#6
|
|||
|
|||
New at Excel and need help on a worksheet
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 On Wed, 25 Feb 2009 20:52:02 -0800, Jennifer wrote: 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 "Gord Dibben" wrote: 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 On Wed, 25 Feb 2009 15:11:01 -0800, Jennifer wrote: 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... |
#7
|
|||
|
|||
New at Excel and need help on a worksheet
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" wrote: 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,R OWS($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 --- "Jennifer" wrote: 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 |
#8
|
|||
|
|||
New at Excel and need help on a worksheet
"Jennifer" wrote:
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 --- |
Thread Tools | |
Display Modes | |
|
|