consolidating and lists

[Guest]

I am tracking the time usage of my management team. I have created five worksheets for each day of the week, and then a sixth which contains a LIST of common tasks which is a dropdown column in the daily worksheets, to simplify their recording. Next to the dropdown LIST column they then indicate the number of 6 minute units spent on that task.

I then want to consildate this data in a 7th summary worksheet so:
Mon Tue Wed Thu Fri Total
(LIST)
TASK1
TASK2
TASK3
TASK4
TASK5

Any suggestions on this consolidation formula?

 

[AH]

Hi russ,

you could try something like this (put this in the crossing of TASK1 and
Mon in your consolidation sheet):

=SUMIF(SheetMon!$A$2:$A$100, A3, SheetMon!$B$2:$B$100)

This checks from the Monday sheet (SheetMon) range A2:A100 if it contains
text in consolidation sheet cell A3 (in this case TASK1) and if so, it sums
the corresponding values from SheetMon!$B$2:$B$100. This you can fill down
to the TASK5 row. Then you can copy this sideways to Fri-column and remember
to change the name of the sheets according to the weekdays.

Totals you can count by simple SUM-function.

Hope this helps.

- Asser

I am tracking the time usage of my management team. I have created five

worksheets for each day of the week, and then a sixth which contains a LIST
of common tasks which is a dropdown column in the daily worksheets, to
simplify their recording. Next to the dropdown LIST column they then
indicate the number of 6 minute units spent on that task.

 

[Guest]

thanks, i'll give it a try. i already devised the solution of running an "if then" for each line on each sheet, I checked to see if it matched that task's text if so then that time was added to the task's total, if not it registered 0. this works fine but is long code.

i then summed those from every sheet on the summary page. a bit longer but i will still gives yours a go cause it is probably faster.