I want to write a function which returns the last date a student attendend
class.
Working from column IV backwards it would be the first cell in each row with
hours recorded.
The spreadsheet is setup like the following:
X Y Z
.........IV
29 Dates= 7/1 7/3 7/8
: LastDate*
33 Name1 7/3 3.2 2.5
34 Name2 7/8 1.5 4.5


Some tried to suggest to me using =INDIRECT(CHAR(LastDate(Row())+#)&"1"),
but it doesn't work.

Thanks in advance your help.
-Stacy

Assume the dates are in Row 1.
Assume the dates start in CELL D1.
Assume you are entering the formula in CELL C3.

Create an array formula (enter the formula then, instead of pressing ENTER,
press CONTROL-SHIFT-ENTER all at the same time.)

Enter the formula:

=INDIRECT(ADDRESS(1,MAX(COLUMN(D3:IV3)*(D3:$IV3<" "))))

after hitting Ctrl-Shift-Enter will appear as...

{=INDIRECT(ADDRESS(1,MAX(COLUMN(D3:IV3)*(D3:$IV3< ""))))}

What the formula says:
Indirect = give the value in the cell address of...
Address = tell me the address in row 1, column...
Max = largest number of
Column = give me the column number of
Cells D3 thru IV3
If the cell isn't blank.

HTH,
--
Gary Brown



"stacyjhaskins" wrote:

I want to write a function which returns the last date a student attendend
class.
Working from column IV backwards it would be the first cell in each row with
hours recorded.
The spreadsheet is setup like the following:
X Y Z
........IV
29 Dates= 7/1 7/3 7/8
: LastDate*
33 Name1 7/3 3.2 2.5
34 Name2 7/8 1.5 4.5


Some tried to suggest to me using =INDIRECT(CHAR(LastDate(Row())+#)&"1"),
but it doesn't work.

Thanks in advance your help.
-Stacy

That worked!

THANK YOU SOOO MUCH!

"Gary Brown" wrote:

Assume the dates are in Row 1.
Assume the dates start in CELL D1.
Assume you are entering the formula in CELL C3.

Create an array formula (enter the formula then, instead of pressing ENTER,
press CONTROL-SHIFT-ENTER all at the same time.)

Enter the formula:

=INDIRECT(ADDRESS(1,MAX(COLUMN(D3:IV3)*(D3:$IV3<" "))))

after hitting Ctrl-Shift-Enter will appear as...

{=INDIRECT(ADDRESS(1,MAX(COLUMN(D3:IV3)*(D3:$IV3< ""))))}

What the formula says:
Indirect = give the value in the cell address of...
Address = tell me the address in row 1, column...
Max = largest number of
Column = give me the column number of
Cells D3 thru IV3
If the cell isn't blank.

HTH,
--
Gary Brown



"stacyjhaskins" wrote:

I want to write a function which returns the last date a student attendend
class.
Working from column IV backwards it would be the first cell in each row with
hours recorded.
The spreadsheet is setup like the following:
X Y Z
........IV
29 Dates= 7/1 7/3 7/8
: LastDate*
33 Name1 7/3 3.2 2.5
34 Name2 7/8 1.5 4.5


Some tried to suggest to me using =INDIRECT(CHAR(LastDate(Row())+#)&"1"),
but it doesn't work.

Thanks in advance your help.
-Stacy