formula to relate last cell of info to another cell

[Karen]

I have created a spreadsheet that shows ppls attendance as the 'y' axis and
the date they attended as the 'x' axis. I want to create a formula that will
tell me the last date they attended.

Im guessing that it would have to start in the row for the person that I am
looking at, search for the last cell with informaion and then cross reference
to the date above.... I hope that makes since....

Help please!!!

 

[T. Valko]

What kind of info is in the cells? Is it text? Is it numeric? Can it be either?

 

[Shane Devenshire]

Hi,

Could you show us a sample of the data layout and what the expected result
would be for that sample.

For example, if the dates for one person was in their own data column you
could just use

=MAX(datacolumn)

It sounds like your data is laid out like this

1/1/2008
Shane
1/3/20008
Shane
....

That is a rather unusual layout so we do need clarification.

Cheers,
Shane Devenshire

 

[Karen]

I have data going lengthwise of ppls names and addresses and then dates of
attendance with an 'x' in the cell for marking attendance and we need to find
a formula that can read all the marks for attendance, pick the last mark in
that row and then gather the corresponding date that is located in row 1 and
then in a seperate column list the date of the last addendance.
ex:

Last Date Attended Name Address 11-2 11-3 11-4
11-5 11-6
11-6 Doe, Jon 1234 1st St x
x x

I don't know the formula or if it is possibile to do this option.

thanks for the help!!

 

[T. Valko]

with an 'x' in the cell for marking attendance

Assume dates are in the range C1:H1

=IF(COUNTIF(C2:H2,"x"),LOOKUP("xxxx",C2:H2,C$1:H$1),"")

 

[Karen]

I tried that formula and Excel did not like it. It gave me an error message.
Maybe my explination was not good enough. I'll try again.
M1: AZ1 has dates and row M2:AZ2 is where we place an 'x' to indicate that a
person was here or not. I want column A to be able to tell me the date in row
M1:AZ1 that has the last "x" in row M2:AZ2

last date 12-1 12-2 12-3 12-4
12-4 x x x

So the formula has to tell Excel to find the last "X" then look at the date
above that lst "X" and tell row A to show that date.

Does that help?

 

[T. Valko]

I tried that formula and Excel did not like it.

It gave me an error message.

It works just fine for me. What kind of error did you get? Post the *exact*
formula that gives you the error message.

Here is the same formula based on your data locations:

=IF(COUNTIF(M2:AZ2,"x"),LOOKUP("xxxx",M2:AZ2,M$1:AZ$1),"")

You'll have to format the formula cell as Date.

Here's another method. Assuming the dates in M1:AZ1 are in ascending order.
Array entered** :

=IF(COUNTIF(M2:AZ2,"x"),MAX(IF(M2:AZ2="x",M$1:AZ$1)),"")

You'll have to format the formula cell as Date.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF(C2:H2="x",C1:H1))