Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 26th 09, 12:09 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 4
Default 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   Report Post  
Old February 26th 09, 01:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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   Report Post  
Old February 26th 09, 01:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 9
Default 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   Report Post  
Old February 26th 09, 01:26 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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   Report Post  
Old February 26th 09, 02:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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
news
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Marked Cells Fred Atkinson Excel Worksheet Functions 3 December 8th 06 06:19 PM
double quotation marked tikchye_oldLearner57 Excel Discussion (Misc queries) 2 August 20th 06 07:44 AM
marked column lucas Excel Discussion (Misc queries) 1 February 7th 06 06:33 PM
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? C-Man23 Excel Worksheet Functions 3 January 19th 06 09:52 AM
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? C-Man23 Excel Worksheet Functions 1 January 9th 06 01:23 PM


All times are GMT +1. The time now is 02:23 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017