Is this possible?

[ssr]

Hi there all, I hope this is possible......

I have 5 worksheets set up identically (one for each employee) with each employee entering data into their own worksheet. These worksheets have columns as follows:

Column A: Employee (they select their employee name from a list of five names)
Column B: Day (they select the numbers 1 to 31 from a list, this column is the day the employee will have to follow up a task that is entered into Description)
Column C: Month (they select from January to December from a list, this column is the month corresponding to Day selected above)
Column D: Actioned (they select "Yes" or "No" from a list, this tells me if the task entered in Description has been followed up(Yes) or not(No))
Column E: Description (they enter text to describe the task they will have to follow up on the Day and Month they entered above)

Ok this is easy enough so far but what I need to do next is create a "Summary" worksheet that will give me, at a glance, the amount of actioned(Yes) and amount of not-actioned(No) records that each employee has entered into their worksheet.

This "Summary" worksheet must also show me week by week the records that are required to be followed up. Basically what I need to see in this summary page is how many records need to be actioned in each particular week.

Please note I do not require the Year value to be considered at all - the day and month entered above is fine.

An example of the "Summary" worksheet required with dummy data:

Month Employee1 Employee2 Employee3 Employee4 ..........................Employee5


January Week 1 2 Actioned 0 Actioned 6 Actioned
5 Not Actioned 10 Not Actioned 1 Not Actioned

January Week 2 9 Actioned 5 Actioned 6 Actioned
3 Not Actioned 10 Not Actioned 9 Not Actioned

January Week 3 5 Actioned 0 Actioned 6 Actioned
5 Not Actioned 10 Not Actioned 1 Not Actioned

January Week 4 1 Actioned 0 Actioned 6 Actioned
5 Not Actioned 10 Not Actioned 1 Not Actioned

I hope I have explained this well, if not please email me (e-mail address removed) for further clarification - and many, many thanks in advance for anyone who can help me with this!
Simon

 

[Mike Waldron]

Hi ss

I would use the countif function explained in the help and referencing the employee spreadsheets. It would look something like this =countif('path[workbook.xls]sheet1'!a1:a25,"yes"
=countif('path[workbook.xls]sheet1'!a1:a25,"no")
That would give you the count of yes and nos for an empolyee. Then repeat for each empolyee changing the workbook. When you open the worksheet you'll be prompted to update the links. All of you summarization will be done when you update

Mike

 

[Tom Ogilvy]

If the employee has to pick their name, then why not have all the data on
one sheet (a master database so to speak). Then you can just use an
autofilter or pivot table to see whatever summary data you need.

--
Regards,
Tom Ogilvy


Hi there all, I hope this is possible......

I have 5 worksheets set up identically (one for each employee) with each
employee entering data into their own worksheet. These worksheets have
columns as follows:

Column A: Employee (they select their employee name from a list of five
names)
Column B: Day (they select the numbers 1 to 31 from a list, this column is
the day the employee will have to follow up a task that is entered into
Description)
Column C: Month (they select from January to December from a list, this
column is the month corresponding to Day selected above)
Column D: Actioned (they select "Yes" or "No" from a list, this tells me if
the task entered in Description has been followed up(Yes) or not(No))
Column E: Description (they enter text to describe the task they will have
to follow up on the Day and Month they entered above)

Ok this is easy enough so far but what I need to do next is create a
"Summary" worksheet that will give me, at a glance, the amount of
actioned(Yes) and amount of not-actioned(No) records that each employee has
entered into their worksheet.

This "Summary" worksheet must also show me week by week the records that are
required to be followed up. Basically what I need to see in this summary
page is how many records need to be actioned in each particular week.

Please note I do not require the Year value to be considered at all - the
day and month entered above is fine.

An example of the "Summary" worksheet required with dummy data:

Month Employee1 Employee2
Employee3 Employee4 ..........................Employee5


January Week 1 2 Actioned 0 Actioned 6
Actioned
5 Not Actioned 10 Not Actioned
1 Not Actioned

January Week 2 9 Actioned 5 Actioned 6
Actioned
3 Not Actioned 10 Not Actioned
9 Not Actioned

January Week 3 5 Actioned 0 Actioned 6
Actioned
5 Not Actioned 10 Not Actioned
1 Not Actioned

January Week 4 1 Actioned 0 Actioned 6
Actioned
5 Not Actioned 10 Not Actioned
1 Not Actioned

I hope I have explained this well, if not please email me (e-mail address removed)
for further clarification - and many, many thanks in advance for anyone who
can help me with this!
Simon