Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
What kind of info is in the cells? Is it text? Is it numeric? Can it be either?
-- Biff Microsoft Excel MVP "Karen" wrote: 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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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" wrote: 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!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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!! "Shane Devenshire" wrote: 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" wrote: 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!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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 ),"") -- Biff Microsoft Excel MVP "Karen" wrote in message ... 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!! "Shane Devenshire" wrote: 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" wrote: 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!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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" wrote: 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 ),"") -- Biff Microsoft Excel MVP "Karen" wrote in message ... 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!! "Shane Devenshire" wrote: 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" wrote: 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!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
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:A Z$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) -- Biff Microsoft Excel MVP "Karen" wrote in message ... 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" wrote: 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 ),"") -- Biff Microsoft Excel MVP "Karen" wrote in message ... 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!! "Shane Devenshire" wrote: 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" wrote: 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!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to relate last cell of info to another cell
Control+shift+enter, not just enter:
=LOOKUP(9.99999999999999E+307,IF(C2:H2="x",C1:H1)) Karen wrote: 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" wrote: 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 ),"") -- Biff Microsoft Excel MVP "Karen" wrote in message ... 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!! "Shane Devenshire" wrote: 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" wrote: 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move cell info and info in neighboring cell on new entry | Excel Discussion (Misc queries) | |||
Combining multiple cells in column to relate to one cell in 1st co | Excel Discussion (Misc queries) | |||
Formula for copying tap info into a cell | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
If Info In Cell Then Formula In Another | Excel Discussion (Misc queries) |