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.