Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Have worksheet with dates across top and Names down the left side as a
attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
You posted in a programming newsgroup, so here is a VB answer...
LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Hi Bill78759,
Let assume column A contain the list of names, the first contain the list of dates, and column (date sorted ascendant from left to right). Let assume Cells A2 to A6 contain the list of names. Cell B1, C1, D1, E1, and F1 contain the list of date (ascending order) In cell G1 Enter €śLast Attended€ť The formula to get the last day to be enter in G2 to G6 for each row is as follow (Change B2:F2 to B3:F3 for 2nd name and so on): =INDIRECT("R1C"&MATCH("?",B2:F2,-1)+1,FALSE) You can lookup how indirect() and match() function work. The +1 after match() is to offset the 1st column to use for name. I tested the code, and it works! Hong Quach "bill78759" wrote: Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
I gave you a "general" solution (looking for *any* text in the row), but you
said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
INDIRECT is a Volatile function and should be avoided where possible... in
this case, the non-Volatile INDEX function can be used instead. See my second posting in this thread to see a formula solution using the INDEX function. -- Rick (MVP - Excel) "Hong Quach" wrote in message ... Hi Bill78759, Let assume column A contain the list of names, the first contain the list of dates, and column (date sorted ascendant from left to right). Let assume Cells A2 to A6 contain the list of names. Cell B1, C1, D1, E1, and F1 contain the list of date (ascending order) In cell G1 Enter €śLast Attended€ť The formula to get the last day to be enter in G2 to G6 for each row is as follow (Change B2:F2 to B3:F3 for 2nd name and so on): =INDIRECT("R1C"&MATCH("?",B2:F2,-1)+1,FALSE) You can lookup how indirect() and match() function work. The +1 after match() is to offset the 1st column to use for name. I tested the code, and it works! Hong Quach "bill78759" wrote: Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Rick, Your first solution
=INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) was better. The solution below hits the first occurrence of text in row 2, not the last as the OP requested. Rick Rothstein wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Thanks Rick. This works but does not give me the LAST occurrence. It gives me
the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Hmm! You are right... what was I thinking (that's what I get for making a
last minute modification to a posting). Thanks for catching that. -- Rick (MVP - Excel) "smartin" wrote in message ... Rick, Your first solution =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) was better. The solution below hits the first occurrence of text in row 2, not the last as the OP requested. Rick Rothstein wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Yes, I made a mistake on that one. Here is the first formula modified for
the range you posted (always a good idea to give all your information in your initial question)... =IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUC T(MAX((E2:AM2<"")*COLUMN(E2:AM2)))-4)) Put this on Row 2 in the column you want the last date shown in and copy it down. Note that I also added a section to suppress the error message if there is no entries for the row in Columns E thru AM. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. This works but does not give me the LAST occurrence. It gives me the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
It is still a good idea to avoid Volatile functions, but the example I
referred you to does not work correctly (see my latest answer to bill78759 for one that does). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... INDIRECT is a Volatile function and should be avoided where possible... in this case, the non-Volatile INDEX function can be used instead. See my second posting in this thread to see a formula solution using the INDEX function. -- Rick (MVP - Excel) "Hong Quach" wrote in message ... Hi Bill78759, Let assume column A contain the list of names, the first contain the list of dates, and column (date sorted ascendant from left to right). Let assume Cells A2 to A6 contain the list of names. Cell B1, C1, D1, E1, and F1 contain the list of date (ascending order) In cell G1 Enter €śLast Attended€ť The formula to get the last day to be enter in G2 to G6 for each row is as follow (Change B2:F2 to B3:F3 for 2nd name and so on): =INDIRECT("R1C"&MATCH("?",B2:F2,-1)+1,FALSE) You can lookup how indirect() and match() function work. The +1 after match() is to offset the 1st column to use for name. I tested the code, and it works! Hong Quach "bill78759" wrote: Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Thanks Rick. I could not get the formula to work. I am sure it was my error,
you did not have the layout of the sheet to work with and my attempt to make adjustments were not successful. I did get a formula from T. Valko from my post in new to Excel (I think that was the group name). I had to make some minor changes (that I picked up from your formula). His was specific to having "x" indicate attendance. I was under the impression that your formula would work regardless what was used to record attendance. I have another worksheet that uses numbers to indicate attendance (indicates the number in a family attending). I have not gotten that to work yet. The file has the Dates in Row 1 starting in Column AO and ending in AS. The results will be in column AW. The names start in row 3. The numbers range from 1 to 5. I tried both formulas you provided making adjustments for where the dates started, etc. without success. Thanks for your help. I am doing this project for my Church and think I have gotten in over my head. I was confident that I could get help in the User Groups as I have in the past. I have used computers for over 30 years as a user but not as a programmer so this is new to me. Also my experience with spreadsheets was with Lotus. Not the same as Excel. Bill "Rick Rothstein" wrote: Yes, I made a mistake on that one. Here is the first formula modified for the range you posted (always a good idea to give all your information in your initial question)... =IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUC T(MAX((E2:AM2<"")*COLUMN(E2:AM2)))-4)) Put this on Row 2 in the column you want the last date shown in and copy it down. Note that I also added a section to suppress the error message if there is no entries for the row in Columns E thru AM. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. This works but does not give me the LAST occurrence. It gives me the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
If you want to take this off-line and send your workbook directly to me,
I'll see if I can find out why it isn't working. If you want to do that, just remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. I could not get the formula to work. I am sure it was my error, you did not have the layout of the sheet to work with and my attempt to make adjustments were not successful. I did get a formula from T. Valko from my post in new to Excel (I think that was the group name). I had to make some minor changes (that I picked up from your formula). His was specific to having "x" indicate attendance. I was under the impression that your formula would work regardless what was used to record attendance. I have another worksheet that uses numbers to indicate attendance (indicates the number in a family attending). I have not gotten that to work yet. The file has the Dates in Row 1 starting in Column AO and ending in AS. The results will be in column AW. The names start in row 3. The numbers range from 1 to 5. I tried both formulas you provided making adjustments for where the dates started, etc. without success. Thanks for your help. I am doing this project for my Church and think I have gotten in over my head. I was confident that I could get help in the User Groups as I have in the past. I have used computers for over 30 years as a user but not as a programmer so this is new to me. Also my experience with spreadsheets was with Lotus. Not the same as Excel. Bill "Rick Rothstein" wrote: Yes, I made a mistake on that one. Here is the first formula modified for the range you posted (always a good idea to give all your information in your initial question)... =IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUC T(MAX((E2:AM2<"")*COLUMN(E2:AM2)))-4)) Put this on Row 2 in the column you want the last date shown in and copy it down. Note that I also added a section to suppress the error message if there is no entries for the row in Columns E thru AM. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. This works but does not give me the LAST occurrence. It gives me the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Thanks - sent offline.
"Rick Rothstein" wrote: If you want to take this off-line and send your workbook directly to me, I'll see if I can find out why it isn't working. If you want to do that, just remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. I could not get the formula to work. I am sure it was my error, you did not have the layout of the sheet to work with and my attempt to make adjustments were not successful. I did get a formula from T. Valko from my post in new to Excel (I think that was the group name). I had to make some minor changes (that I picked up from your formula). His was specific to having "x" indicate attendance. I was under the impression that your formula would work regardless what was used to record attendance. I have another worksheet that uses numbers to indicate attendance (indicates the number in a family attending). I have not gotten that to work yet. The file has the Dates in Row 1 starting in Column AO and ending in AS. The results will be in column AW. The names start in row 3. The numbers range from 1 to 5. I tried both formulas you provided making adjustments for where the dates started, etc. without success. Thanks for your help. I am doing this project for my Church and think I have gotten in over my head. I was confident that I could get help in the User Groups as I have in the past. I have used computers for over 30 years as a user but not as a programmer so this is new to me. Also my experience with spreadsheets was with Lotus. Not the same as Excel. Bill "Rick Rothstein" wrote: Yes, I made a mistake on that one. Here is the first formula modified for the range you posted (always a good idea to give all your information in your initial question)... =IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUC T(MAX((E2:AM2<"")*COLUMN(E2:AM2)))-4)) Put this on Row 2 in the column you want the last date shown in and copy it down. Note that I also added a section to suppress the error message if there is no entries for the row in Columns E thru AM. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. This works but does not give me the LAST occurrence. It gives me the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Occurance
Thanks Rick. Rick fixed the problem when he got the data.
Bill "Rick Rothstein" wrote: If you want to take this off-line and send your workbook directly to me, I'll see if I can find out why it isn't working. If you want to do that, just remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. I could not get the formula to work. I am sure it was my error, you did not have the layout of the sheet to work with and my attempt to make adjustments were not successful. I did get a formula from T. Valko from my post in new to Excel (I think that was the group name). I had to make some minor changes (that I picked up from your formula). His was specific to having "x" indicate attendance. I was under the impression that your formula would work regardless what was used to record attendance. I have another worksheet that uses numbers to indicate attendance (indicates the number in a family attending). I have not gotten that to work yet. The file has the Dates in Row 1 starting in Column AO and ending in AS. The results will be in column AW. The names start in row 3. The numbers range from 1 to 5. I tried both formulas you provided making adjustments for where the dates started, etc. without success. Thanks for your help. I am doing this project for my Church and think I have gotten in over my head. I was confident that I could get help in the User Groups as I have in the past. I have used computers for over 30 years as a user but not as a programmer so this is new to me. Also my experience with spreadsheets was with Lotus. Not the same as Excel. Bill "Rick Rothstein" wrote: Yes, I made a mistake on that one. Here is the first formula modified for the range you posted (always a good idea to give all your information in your initial question)... =IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUC T(MAX((E2:AM2<"")*COLUMN(E2:AM2)))-4)) Put this on Row 2 in the column you want the last date shown in and copy it down. Note that I also added a section to suppress the error message if there is no entries for the row in Columns E thru AM. -- Rick (MVP - Excel) "bill78759" wrote in message ... Thanks Rick. This works but does not give me the LAST occurrence. It gives me the first occurrence. I am not sure how to use the first example you gave but I do have another work sheet that has numbers instead of x. Of course I get an error when there is no x, but I can solve that - the list is not that I can't just replace the error message with blank. The actual cells involved are Dates E1:AM1, First column is E2. and goes to E346. "Rick Rothstein" wrote: I gave you a "general" solution (looking for *any* text in the row), but you said you were marking the attendance with an "X"... so here is a simpler formula that keys off of that fact (for Row 2 in this example formula)... =INDEX(B$1:J$1,1,MATCH("X",B2:J2,0)) Again, change the ranges to suit your set up and copy down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You posted in a programming newsgroup, so here is a VB answer... LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column To do other rows, just change the 2 in the Cells property call to whatever row number you want (I would presume that would be a For..Next index counter. If, on the other hand, you were looking for a worksheet formula solution, put this on Row 2 (assumed to be the first student's row) in whatever column you will track the last attendance date in and copy it down... =INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<"")*COLUMN (B2:J2)))-1) Adjust the top end of the ranges (all the J's to whatever column letter(s) your last possible date can be in). Note the that final -1 is needed because the dates are assumed to start in Column B. If the dates actually start in Column C, then change the -1 to -2. -- Rick (MVP - Excel) "bill78759" wrote in message ... Have worksheet with dates across top and Names down the left side as a attendance record. Need to examine each Name row to find that last time the individual attended (attendance is indicated by x's in the row). The date would be from the top row in the column corresponding to the x. I first posted this in the New User group and have one response, but I need answer asap. I know that most responders are volunteers and therefore answers are given as time permits. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
the Last occurance | Excel Discussion (Misc queries) | |||
Re-occurance | Excel Discussion (Misc queries) | |||
first and last occurance | Excel Worksheet Functions | |||
Find the next occurance | Excel Worksheet Functions | |||
occurance in each year | Excel Worksheet Functions |