Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Marking cell; totaling all # between marked cell & next marked cel
Hello,
I have created a workbook, including many worksheets for each month of the year. The first row of each worksheet indicates the days of the month. The first column lists all students in our classroom. The purpose of the workbook is to keep track of attendance. The last column of each worksheet totals the attendance hours/month for each student. Problem: I need to somehow mark/highlight a cell (any particular date for any particular student - dates will vary depending on the student) that would show their last test date. Then I need to create another column at the end of each sheet that would total the attendance hours for each student since their last test. That is, when they take another test, I will need to mark another cell/date (to indicate their new test date) and the total hours since their last test will need to reset. The "total hours since their last test date" will reset everytime they take another test...and begin summing from the most recent test date each time. Is their a formula that can accomplish this? If so, will it extend to the next worksheet/month to count the hours between tests seamlessly? I would greatly appreciate any help with this issue! Many thanks!!! -- Jessie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Marking cell; totaling all # between marked cell & next marked cel
It can be done but you will have to handle lot of complexity...
Like how many tests in a month? It will be easier if there is at most one... If you have more than one then which cell you will have the total...? "Marley" wrote: Hello, I have created a workbook, including many worksheets for each month of the year. The first row of each worksheet indicates the days of the month. The first column lists all students in our classroom. The purpose of the workbook is to keep track of attendance. The last column of each worksheet totals the attendance hours/month for each student. Problem: I need to somehow mark/highlight a cell (any particular date for any particular student - dates will vary depending on the student) that would show their last test date. Then I need to create another column at the end of each sheet that would total the attendance hours for each student since their last test. That is, when they take another test, I will need to mark another cell/date (to indicate their new test date) and the total hours since their last test will need to reset. The "total hours since their last test date" will reset everytime they take another test...and begin summing from the most recent test date each time. Is their a formula that can accomplish this? If so, will it extend to the next worksheet/month to count the hours between tests seamlessly? I would greatly appreciate any help with this issue! Many thanks!!! -- Jessie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Marking cell; totaling all # between marked cell & next marked
I was afraid it would be very complex...
There will only be one test per month. -- Jessie "Sheeloo" wrote: It can be done but you will have to handle lot of complexity... Like how many tests in a month? It will be easier if there is at most one... If you have more than one then which cell you will have the total...? "Marley" wrote: Hello, I have created a workbook, including many worksheets for each month of the year. The first row of each worksheet indicates the days of the month. The first column lists all students in our classroom. The purpose of the workbook is to keep track of attendance. The last column of each worksheet totals the attendance hours/month for each student. Problem: I need to somehow mark/highlight a cell (any particular date for any particular student - dates will vary depending on the student) that would show their last test date. Then I need to create another column at the end of each sheet that would total the attendance hours for each student since their last test. That is, when they take another test, I will need to mark another cell/date (to indicate their new test date) and the total hours since their last test will need to reset. The "total hours since their last test date" will reset everytime they take another test...and begin summing from the most recent test date each time. Is their a formula that can accomplish this? If so, will it extend to the next worksheet/month to count the hours between tests seamlessly? I would greatly appreciate any help with this issue! Many thanks!!! -- Jessie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Marking cell; totaling all # between marked cell & next marked
Can you send the file to me? Mark the headings where you want the result...
I am assuming that the date will be same for all students.. If you have dates from 1-31 on each sheet from B1:AF1 then if you enter the test date in AG1 (say 6) and enter the following in AG2 =SUM(INDIRECT("R"&ROW()&"C"&($AG$1+1)&":R"&ROW()&" C32",0)) and copy down... to get the sum from the date of the test (including the date) You will have to add an IF statement to pick from the previous month.... "Jessie" wrote: I was afraid it would be very complex... There will only be one test per month. -- Jessie "Sheeloo" wrote: It can be done but you will have to handle lot of complexity... Like how many tests in a month? It will be easier if there is at most one... If you have more than one then which cell you will have the total...? "Marley" wrote: Hello, I have created a workbook, including many worksheets for each month of the year. The first row of each worksheet indicates the days of the month. The first column lists all students in our classroom. The purpose of the workbook is to keep track of attendance. The last column of each worksheet totals the attendance hours/month for each student. Problem: I need to somehow mark/highlight a cell (any particular date for any particular student - dates will vary depending on the student) that would show their last test date. Then I need to create another column at the end of each sheet that would total the attendance hours for each student since their last test. That is, when they take another test, I will need to mark another cell/date (to indicate their new test date) and the total hours since their last test will need to reset. The "total hours since their last test date" will reset everytime they take another test...and begin summing from the most recent test date each time. Is their a formula that can accomplish this? If so, will it extend to the next worksheet/month to count the hours between tests seamlessly? I would greatly appreciate any help with this issue! Many thanks!!! -- Jessie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Marking cell; totaling all # between marked cell & next marked cel
Let's see if this is what you want:
T = test ...........A...........B..........C 1....Header..Header..Header 2.......10.............................. 3.......12.............................. 4.......14..........T..........36... 5.......10............................. 6........4...........T..........14... 7........2.............................. 8........2.............................. 9........2...........T...........6... If that's what you want enter this formula in C2 and copy down as needed: =IF(B2="T",SUM(A$2:A2)-SUM(C$1:C1),"") -- Biff Microsoft Excel MVP "Marley" wrote in message ... Hello, I have created a workbook, including many worksheets for each month of the year. The first row of each worksheet indicates the days of the month. The first column lists all students in our classroom. The purpose of the workbook is to keep track of attendance. The last column of each worksheet totals the attendance hours/month for each student. Problem: I need to somehow mark/highlight a cell (any particular date for any particular student - dates will vary depending on the student) that would show their last test date. Then I need to create another column at the end of each sheet that would total the attendance hours for each student since their last test. That is, when they take another test, I will need to mark another cell/date (to indicate their new test date) and the total hours since their last test will need to reset. The "total hours since their last test date" will reset everytime they take another test...and begin summing from the most recent test date each time. Is their a formula that can accomplish this? If so, will it extend to the next worksheet/month to count the hours between tests seamlessly? I would greatly appreciate any help with this issue! Many thanks!!! -- Jessie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Marked Cells | Excel Worksheet Functions | |||
double quotation marked | Excel Discussion (Misc queries) | |||
marked column | Excel Discussion (Misc queries) | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions |