Counting columns, adding totals #2
 

Hi. Been working with this excel file that needs tweaking. The more tweaks
I add, the more the client wants. Which is a good thing<g

To restate: This xls file consists of 7 worksheets, one of every day of the
week. Each
worksheet has a 30 column section, each marked either [X] or [ ]. There's
one row for each employee, the columns represent their 1/2 hour schedule.

I'm using COUNTIF() at the top of the 30 columns. There's also a COUNTIF()
column for row totals, department sub-totals and grand totals. Neat!

This weekend I plan to add department summary sheets for each day of the
week plus a grand summary sheet. Again, neat! I still need a couple of
things:

1) The client wants a button next to each employee name. When you hit the
button, you'd get a dialog box asking for a start time-end time range. This
range would fill in all the [X]s needed for that row. I'm thinking a VBA
script?

2) The client also wants an employee summary sheet. A given employee will
work in different departments, depending on the day. Is it possible to
write a script to search all the rows of each sheet to find a particular
employee and add up this numbers? I can get the client to add an employee #
column next to their name, for uniqueness testing.

I really appreciate the help thus far. Thanks...Dennis

Counting columns, adding totals #2
 

#1 sounds like vba with input box. for #2 try using sumif
command with criteria being employee name or number.


-----Original Message-----
Hi. Been working with this excel file that needs
tweaking. The more tweaks
I add, the more the client wants. Which is a good
thing<g

To restate: This xls file consists of 7 worksheets, one
of every day of the
week. Each
worksheet has a 30 column section, each marked either [X]
or [ ]. There's
one row for each employee, the columns represent their
1/2 hour schedule.

I'm using COUNTIF() at the top of the 30 columns.
There's also a COUNTIF()
column for row totals, department sub-totals and grand
totals. Neat!

This weekend I plan to add department summary sheets for
each day of the
week plus a grand summary sheet. Again, neat! I still
need a couple of
things:

1) The client wants a button next to each employee
name. When you hit the
button, you'd get a dialog box asking for a start time-
end time range. This
range would fill in all the [X]s needed for that row.
I'm thinking a VBA
script?

2) The client also wants an employee summary sheet. A
given employee will
work in different departments, depending on the day. Is
it possible to
write a script to search all the rows of each sheet to
find a particular
employee and add up this numbers? I can get the client
to add an employee #
column next to their name, for uniqueness testing.

I really appreciate the help thus far. Thanks...Dennis

.

Counting columns, adding totals #2
 

Dennis,

I would be very leery of adding hundreds? of buttons to a spreadsheet.
In addition, I would watch the workbook size and not let it become "too large".
Excel has a tendency to take either circumstance as an opportunity to become rebellious.

Consider using the worksheet before double-click event instead of multiple buttons.
The "target" cell could identify the employee name, the same as pressing a button.
Also, some dependable backup routine for the workbook would be mandatory.

Regards,
Jim Cone
San Francisco, CA

"Dennis Allen" wrote in message ...
Hi. Been working with this excel file that needs tweaking. The more tweaks
I add, the more the client wants. Which is a good thing<g

To restate: This xls file consists of 7 worksheets, one of every day of the
week. Each
worksheet has a 30 column section, each marked either [X] or [ ]. There's
one row for each employee, the columns represent their 1/2 hour schedule.

I'm using COUNTIF() at the top of the 30 columns. There's also a COUNTIF()
column for row totals, department sub-totals and grand totals. Neat!

This weekend I plan to add department summary sheets for each day of the
week plus a grand summary sheet. Again, neat! I still need a couple of
things:

1) The client wants a button next to each employee name. When you hit the
button, you'd get a dialog box asking for a start time-end time range. This
range would fill in all the [X]s needed for that row. I'm thinking a VBA
script?

2) The client also wants an employee summary sheet. A given employee will
work in different departments, depending on the day. Is it possible to
write a script to search all the rows of each sheet to find a particular
employee and add up this numbers? I can get the client to add an employee #
column next to their name, for uniqueness testing.

I really appreciate the help thus far. Thanks...Dennis