Tally function assistance

[AnimalMagic]

Hey folks,

I have a task tracking spreadsheet that keeps track of several tasks in
a day, which involve several "Jobs" or projects. It tallies hours per
day, per week, and per task ticket quite well, but I have yet to add per
job totals for each day and for the week.

I want to add a sheet that tallies a day on a per job basis, such that
all hours for a given job are summed up and show up in the tally sheet.

I have a cell fill that gets chosen from a drop down menu from a sheet
that gets actual job numbers placed in it and a description. The
description field auto-fills via vlookup after the job number is chosen.

The hours accrued for each ticket fills in after the start and stop
times are entered, and the day and week tally are at the bottom of each
sheet.

I want to take all task tickets that were entered against a given job to
be tallied so that the total time on that J/N for the day is given.

IOW if I worked on 12 tasks in a day, and 4 are duplicates, I need
those sub-totaled on the tally sheet as well as totals for the single
entries.

Is the operation I need to examine specific cell contents on the sheet
for J/Ns, and then sum the hour totals for each of those tickets for that
J/N on that day an "If" operator or is there a "for"(each).

The J/N cells are in the same column, but are separated by several rows
between each task ticket entry, so I suppose I would poll specific cells.

I also have the drop down list for the J/Ns, but it carries all the
blank cells in the lookup table,and is always positioned over the blank
cells. Is there a way to make a dynamic array from the JobList table
that only shows an array of cells that had data in them? It works as is,
but would be nice not to see all the blank cells in the drop down. Are
there drop down lists that vary in size according to whether data exists
in a cell?

 

[Bernie Deitrick]

Use a pivot table based on your data table. Use J/N as the row field, and hours as the data field,
and format the cells as [h]:mm.

As for the dropdown, use a dynamic range name, like JNList, where you define the name JNList as

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

where your jobs list starts in cell A1, and are the only entries in column A.
Then in your Data Validation list source, use

=JNList

HTH,
Bernie
MS Excel MVP

 

[AnimalMagic]

Use a pivot table based on your data table. Use J/N as the row field, and hours as the data field,
and format the cells as [h]:mm.

As for the dropdown, use a dynamic range name, like JNList, where you define the name JNList as

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

where your jobs list starts in cell A1, and are the only entries in column A.
Then in your Data Validation list source, use

=JNList

HTH,
Bernie
MS Excel MVP

Thanks. The dropdown works.

 

[AnimalMagic]

snip

Hey, is there a way to define a window in which this dynamic array
operate, as opposed to "all cells from this point on"?

Such that I may have column headings that won't appear in the list?

So, like, from A2 through A36 for a 35 row array. That way I can
utilize a row for column naming "headers".

 

[Bernie Deitrick]

Sure. With the header in row 1:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

HTH,
Bernie
MS Excel MVP