OK, I've read ad nauseam all the posts about finding the last row in a column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and the
latest test date at row 61. If a person did not take the test, the column is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

Maybe this

=LOOKUP(10^23,A14:A61)

Format the cell as a date

Mike

"fedude" wrote:

OK, I've read ad nauseam all the posts about finding the last row in a column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and the
latest test date at row 61. If a person did not take the test, the column is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

Mike,

Two other pieces of information:

1) The dates of the tests are in column B, scores start in column C:DZ
2) The scores may be any number from 0 to 100 and not in any sorted order.
The highest score may not be the last score.

Maybe I just don't know how the LOOKUP function works, but with only 2
parameters, it's using the array form. Won't the result be the highest score
not the last score?



"Mike H" wrote:

Maybe this

=LOOKUP(10^23,A14:A61)

Format the cell as a date

Mike

"fedude" wrote:

OK, I've read ad nauseam all the posts about finding the last row in a column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and the
latest test date at row 61. If a person did not take the test, the column is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
OK, I've read ad nauseam all the posts about finding the last row in a
column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and
the
latest test date at row 61. If a person did not take the test, the column
is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find
the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

Don, you are almost my hero. This formula looks simple, but..... I probably
wasn't clear in my description

I can't seem to get this to work. Probably because I don't understand how
array functions work. Partly because I can't figure out anyway MAX will work
for this problem. I believe I entered it properly because I can see the
braces.

In your formula, I don't understand what you use H1 for? The student names
are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
looking for the last cell in the 14:61 range of each column that has
anything in it. I'll put this formula in row 136 of each column. This
formula will tell me the date of the last test score in the 14:61 range in
that column

The test dates are in B14:B61. Only column B has dates in them.
The test scores are in the next 200 columns in the same rows as the dates.


"Don Guillett" wrote:

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
OK, I've read ad nauseam all the posts about finding the last row in a
column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and
the
latest test date at row 61. If a person did not take the test, the column
is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find
the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

You may send your workbook to my address below along with a snippet of this
page and I'll take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
Don, you are almost my hero. This formula looks simple, but..... I
probably
wasn't clear in my description

I can't seem to get this to work. Probably because I don't understand how
array functions work. Partly because I can't figure out anyway MAX will
work
for this problem. I believe I entered it properly because I can see the
braces.

In your formula, I don't understand what you use H1 for? The student
names
are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
looking for the last cell in the 14:61 range of each column that has
anything in it. I'll put this formula in row 136 of each column. This
formula will tell me the date of the last test score in the 14:61 range in
that column

The test dates are in B14:B61. Only column B has dates in them.
The test scores are in the next 200 columns in the same rows as the dates.


"Don Guillett" wrote:

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
OK, I've read ad nauseam all the posts about finding the last row in a
column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and
the
latest test date at row 61. If a person did not take the test, the
column
is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have
the
last score. So I need to search back starting at row 61 to row 14 to
find
the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I
came
up empty. My thought was to insert a custom function in each column.

TIA!

=IF(ISNA(MATCH(999999,C14:C61)),"",INDEX($B:$B,MAT CH(999999,C14:C61)+13))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
You may send your workbook to my address below along with a snippet of
this page and I'll take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
Don, you are almost my hero. This formula looks simple, but..... I
probably
wasn't clear in my description

I can't seem to get this to work. Probably because I don't understand
how
array functions work. Partly because I can't figure out anyway MAX will
work
for this problem. I believe I entered it properly because I can see the
braces.

In your formula, I don't understand what you use H1 for? The student
names
are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
looking for the last cell in the 14:61 range of each column that has
anything in it. I'll put this formula in row 136 of each column. This
formula will tell me the date of the last test score in the 14:61 range
in
that column

The test dates are in B14:B61. Only column B has dates in them.
The test scores are in the next 200 columns in the same rows as the
dates.


"Don Guillett" wrote:

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
OK, I've read ad nauseam all the posts about finding the last row in a
column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a
test
scores. They are by date with the earliest date starting at row 14
and
the
latest test date at row 61. If a person did not take the test, the
column
is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have
the
last score. So I need to search back starting at row 61 to row 14 to
find
the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I
came
up empty. My thought was to insert a custom function in each column.

TIA!

Perfect.... You are my hero!

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
Perfect.... You are my hero!