increase cell count upon changes per day...

[glenathon]

Hi there, Kinda new to Excel, but I'm a fast learner and I think I may have found something just beyond my abilities....My intentions are as follows...

Every day I open my spreadsheet and add values to cells....similar to a teacher adding test scores of a student. I plan on creating a weekly calendar inside of Excel on my current spreadsheet...something simple that says Monday, Tuesday, Wednesday, Thursday, Friday...that's it. I'd like Excel to note as I add a test score, to increase the number of evaluations I did to the associated day I added the test score.

How's that for stream of consciousness? heheh...lemme try again...

When I open the spreadsheet....on a Monday for example, I'd like Excel to note if a value increases in any of 7 particular cells, to add '1' to the Monday cell. The next day when I come in, if I do an evaluation, I'd like it to note that I added an evaluation on Tuesday and mark it in the appropriate box.

Basically, I'm trying to count how many evaluations I do per day...so when I open it on a Tuesday, it will add the number to the Tueday cell Can this be done somehow? I assumed that Excel would use the NOW function and use the date from the system clock or something to determine where to place the value.

Thank you so much in advance for your help.

 

[lenze]

Welcome to the board!!

Yes, this can be done, but first a few questions.

1. When you say 7 different cells, do you mean you do 7 different types of evaluations or do you mean you evaluate 7 different individuals?

2. Where are these 7 cells located and how do they update, by input or by calculation?

3. Can you post an example using Colo's html add-in, available at the link below?

lenze

 

[glenathon]

Hi Lenze....thanks for the reply, and the nice welcome.

Unfortunately, the spreadsheet is on a different computer from where I'm replying, but I will try to get an example up to you shortly.

To answer your questions:

1) Right now, when I add an evaluation (test score) to a blank cell, another cell uses the COUNT function and increments by one. My spreadsheet is seperated into 7 sections, or TEAMS of students. When I do an evaluation on Team 1, the number of evaluations done for team 1 increases by one. I would like my calendar idea to see if any one of the total evaluations cells I have for each team (7 total eval. cells, one for each team) has increased, then add that number to whatever day I happen to be working on the sheet. If it would be easier to accomplish this, I can just use the cells that I input the test score into, rather than a cell that already has a formula in it.

2) They update by COUNT

3) I'll give that a shot ASAP.

I hope that helps you imagine what I'm trying to accomplish. Thanks again for your reply!

 

[lenze]

I think I have an idea of what you want. I will think about it and wait for your example posting to give it a stab. Might be a day or two, so, if anyone else wants to try, please do.

lenze

 

[glenathon]

Hmmm.....forgive me, but I'm having difficulties using this Macro. I don't know if it's because I'm on a Mac, or because I'm a n00b and havne't used a macro in Excel before...but I'm getting a variety of errors.

Any advice you can provide would be great. Otherwise, I will attempt to try it on my Windows machine where the original file resides tomorrow.

Thanks again.

 

[lenze]

Hmmm.....forgive me, but I'm having difficulties using this Macro. I don't know if it's because I'm on a Mac, or because I'm a n00b and havne't used a macro in Excel before...but I'm getting a variety of errors.

Any advice you can provide would be great. Otherwise, I will attempt to try it on my Windows machine where the original file resides tomorrow.

Thanks again.

I'm not sure what macro you're refering to. Do you mean Colo's add-in? If so, see the FAQ's

http://www.mrexcel.com/board2/viewtopic.php?t=92622

After thinking about your question, I believe it can be done with formulas or via a Worksheet Change event. However, neither would be my first choice. I would rearrange the data into a database type table and use one of the most useful tools in Excel, The PIVOT TABLE. If you are not familiar with PTs, see the articles about them here

http://www.mrexcel.com/articles.shtml

When you are able to post your example data, I will try to constuct one you can use to get you started.

lenze

 

[glenathon]

Hi Lenze. Thank you for the information. I am unable to get to the Windows Machine to test this, but I was able to locate a copy of the spreadsheet. I have uploaded it to the following site:

http://tinyurl.com/a72hd

I hope that will suffice. This is my first spreadsheet, so be gentle.

Under the 'Call' and 'Email' row, I insert a number. A number of formulas work their magic from there. My intention is to add an additional section above the 'Exemptions' section that says Monday, Tuesday, Wednesday...etc and upon opening the doc on that specific day, it will tell me how many evaluations I had done on that day.

Thank you again for all of your help on this one!