Link data in worksheets? Add/deduct formulas as criteria are/aren't met?

[amytr1122]

Hello all,

I am a newbie to Excel, therefore, any help is greatly appreciated!!

What can I do to link my worksheets together? I need the months to link from january to december.

I'm trying to come up with a system to maintain attendance of employees. How do I come up with a rolling system to add and deduct the points as their attendance changes? The goal of each employee is to have the least amount of points as possible, because he/she will be penalized once they receive a certain amount of points.

Let's say that Jane was hired in October. She has accumulated 10 points by December from tardies, call-ins, etc. By the end of January, she has perfect attendance; she is awarded half a point to be taken off toward her 10, giving her 9.5. In February, there is a day she does not show up, so now her total goes up to 12.5 (3 pointed added because of NCNS-no show no call). In March and April, she has perfect attendance, so now her total is 11.5. (half a point allowed for each month with perfect attendance)

By rolling, I mean a formula that will calculate continuously over month after month and have the points roll over month to month.

THANK YOU!!!!!

[icestationzbra]

your note says that you have solved one part, is it the #1 above?

if not, then, you could use the following formula in January!AJ5:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

can you explain in greater detail about the Rolling System? i am unable to understand it.

for every month that an employee has perfect attendance, does it mean that the Total Points column should have a 0 (zero) for that employee in that month? now, if s/he has 0, you want to give them 0.5, isn't that kind of unfair :-D...

if you give an employee 1/2 a point for a perfect month, it will total up to 6 points from January up to, and including, December, not 10, if you consider only a single calendar year. do you mean to span calendar years, spill over from one to another? hypothetically, if an employee has reached 10 in December, why would we not add 0.5 and make it 10.5 in January, instead of 9.5 like you stated?

i am not sure that i have any idea on how to approach this second conundrum, but greater detail may help wiser souls here...

[amytr1122]

icestationzbra,

First off, thank you so much for replying!!!

The part that I have figured out myself, I have deleted from the message. So I still need help on the remaining parts.
I entered the formula that you have provided for the point system, however, it is not calculating the total points correctly. I have attached another file with the formula put in, showing that the total calculated points are not correct. I am not sure why!

In the second part, I gave examples of numbers for the point system, so you are correct when you said the numbers do not add up.
My wording was unclear, I apologize!

The goal of each employee is to have the least amount of points as possible, because he/she will be penalized once they receive a certain amount of points.
Let's say that Amy was hired in October. She has accumulated 10 points by December from tardies, call-ins, etc. By the end of January, she has perfect attendance; she is awarded half a point to be taken off toward her 10, giving her 9.5. In February, there is a day she does not show up, so now her total goes up to 12.5. In March and April, she has perfect attendance, so now her total is 11.5. (half a point allowed for each month with perfect attendance)

By rolling, I mean a formula that will calculate continuously over month after month, year after year, so yes, spilling over from one to another.

Again, thank you so much for your help. It is greatly appreciated. In the meantime, I am still trying to improve my skills on excel!

[Pete_UK]

You can use this formula in AJ5:

=tblJanuary[[#This Row],[CI]]*1+tblJanuary[[#This Row],[T]]*0.5+tblJanuary[[#This Row],[NCNS]]*3

then copy down. You need to adjust this to show 1 dp.

Hope this helps.

Pete

[icestationzbra]

on January tab, in AG5:

Please Login or Register  to view this content.

then, drag-fill right to AH5 and AI5.

put this formula in AJ5:

Please Login or Register  to view this content.

then, format the cell AJ5 as CUSTOM > General;;;

now, if the formulae do not propagate automatically to row 9, select cells AG5 through AJ5 and drag-fill down to row 9.

your recent explanation of Rolling Over makes things much clearer. i will ponder over it; i hope you are not averse to using additional "helper" columns should the need arise.

[amytr1122]

Thank you, this definitely helps!

[amytr1122]

Thank you! This works. I am open to anything that you suggest, helper columns included!

[amytr1122]

Excuse me for not knowing this, but how do I apply those formulas to the remaining months?
=[@CI]*1+[@T]*0.5+[@NCNS]*3
When I enter this into the February one, it becomes invalid.

=tblJanuary[[#This Row],[CI]]*1+tblJanuary[[#This Row],[T]]*0.5+tblJanuary[[#This Row],[NCNS]]*3
I also tried replacing the "January" with "February," and it is still invalid.

My problem is that I don't know much about these formulas, so I don't know where to start to edit them

[Pete_UK]

You can type the formula in as this:

=AG5*1+AH5*0.5+AI5*3

and it should copy down to the bottom of your table. Then you can just <copy> that cell, click to the same cell in the February sheet, then CTRL-V, then click to the March sheet, same cell, and CTRL-V again, and so on for the other months. Press <Esc> when you've finished.

Hope this helps.

Pete

[amytr1122]

Thank you, it worked!