Counting columns, adding totals #2

[Dennis Allen]

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

 

[CLR]

Hi Dennis...........

1-Yes, VBA is the way to go to get your dialog boxes to pop up and to do the
calculations to fill in your "X's".........but if you want someone to write
it for you, you'll need to supply many more specifics...........

2-Have a look at Data > subtotals.........and/or Data > Filter >
AutoFilter...........one or the other may supply what you want
here............IMHO the employee number addition would be a good thing for
the long run..............

hth
Vaya con Dios,
Chuck, CABGx3

 

[Dennis Allen]

Hi Dennis...........

1-Yes, VBA is the way to go to get your dialog boxes to pop up and to do the
calculations to fill in your "X's".........but if you want someone to write
it for you, you'll need to supply many more specifics...........

A given row has 16 columns, representing 1/2 hour increments of the day. We
need a button on this row to prompt for a time range. If you fill
9:00-11:30, then the first 6 columns need to be filled with [X], the rest
blanked out.

2-Have a look at Data > subtotals.........and/or Data > Filter >
AutoFilter...........one or the other may supply what you want
here............IMHO the employee number addition would be a good thing for
the long run..............

Well, if I might have employe #12345 in , row 5 department A on the Monday
sheet. On the sheet Tuesday he might be row 55, department D. Next week
might have be in different departments altogether. Yet in the employee
summary sheet I need to find employee #12345 on all the sheets and add up
his numbers.

 

[CLR]

Hi Dennis..........

I'm not sure all of what you're doing, but it sounds to me like you would be
a lot better off if you changed the Seven sheets all into one Database on
one sheet.......might have to add a column or two to get everything in, but
then your sorting and reporting could be easy with AutoFilter......you could
select each day of the week, each employee by number, each department, and
any combination of them, and AutoFilter would sort them out for
you.....................as for the macro to fill in the X's........I suppose
it would be a jazzy thing to program, with the pop-up selection box and all,
but the actual data-entry person could probably do it just as fast by
hand.........copy and paste.........or a simple macro that would just put
"X" in all the highlighted cells in a row............or, you could just
enter the start time in column B and the end time in column C and put your
half hour increments of time in row one starting with say 8:00 in D1, then
put the following formula in D2 and copy over and down.........this way you
would have a record of the actual start/finish times if maybe you wanted to
do other calculations in the future ..........

=IF(AND(D$1>=$B2,D$1<=$C2),"X","")

(small workbook available if you want it emailed,.....
filename:XforHours.xls)
hth
Vaya con Dios,
Chuck, CABGx3

Hi Dennis...........

1-Yes, VBA is the way to go to get your dialog boxes to pop up and to do the
calculations to fill in your "X's".........but if you want someone to write
it for you, you'll need to supply many more specifics...........

A given row has 16 columns, representing 1/2 hour increments of the day. We
need a button on this row to prompt for a time range. If you fill
9:00-11:30, then the first 6 columns need to be filled with [X], the rest
blanked out.

2-Have a look at Data > subtotals.........and/or Data > Filter >
AutoFilter...........one or the other may supply what you want
here............IMHO the employee number addition would be a good thing for
the long run..............

Well, if I might have employe #12345 in , row 5 department A on the Monday
sheet. On the sheet Tuesday he might be row 55, department D. Next week
might have be in different departments altogether. Yet in the employee
summary sheet I need to find employee #12345 on all the sheets and add up
his numbers.

 

[Dennis Allen]

Well, perhaps we'll convert the seven sheets into a database at some point.
Right now I just want to stick with the client request.

On a given sheet, each row is an employee. We need a button on each row.
This button activate a function. This function would activate a dialog box,
prompting the user for a start time, end time, and a break time. For our 16
columns, we would fill any column within our time range with [X], the break
column would get a , the rest would be blank.

I'm not sure how I could translate a time 99:99 prompt. If the button is on
column C, 9:30 = column D, 10:00 = column E, etc. I imagine each button
would activate the function, feeding it the current col/row. By placing a
column of buttons before our set of 16 columns, the same function could
serve all rows and even all sheets.

If you have a sample, please email to it me (remove the XXX)
(e-mail address removed)

 

[CLR]

Hi Dennis...........

Sample workbook sent by email.........hope it helps.

Vaya con Dios,
Chuck, CABGx3

Well, perhaps we'll convert the seven sheets into a database at some point.
Right now I just want to stick with the client request.

On a given sheet, each row is an employee. We need a button on each row.
This button activate a function. This function would activate a dialog box,
prompting the user for a start time, end time, and a break time. For our 16
columns, we would fill any column within our time range with [X], the break
column would get a , the rest would be blank.

I'm not sure how I could translate a time 99:99 prompt. If the button is on
column C, 9:30 = column D, 10:00 = column E, etc. I imagine each button
would activate the function, feeding it the current col/row. By placing a
column of buttons before our set of 16 columns, the same function could
serve all rows and even all sheets.

If you have a sample, please email to it me (remove the XXX)
(e-mail address removed)

Hi Dennis..........

I'm not sure all of what you're doing, but it sounds to me like you

would

be
a lot better off if you changed the Seven sheets all into one Database on
one sheet.......might have to add a column or two to get everything in, but
then your sorting and reporting could be easy with AutoFilter......you could
select each day of the week, each employee by number, each department, and
any combination of them, and AutoFilter would sort them out for
you.....................as for the macro to fill in the X's........I suppose
it would be a jazzy thing to program, with the pop-up selection box and all,
but the actual data-entry person could probably do it just as fast by
hand.........copy and paste.........or a simple macro that would just put
"X" in all the highlighted cells in a row............or, you could just
enter the start time in column B and the end time in column C and put your
half hour increments of time in row one starting with say 8:00 in D1, then
put the following formula in D2 and copy over and down.........this way you
would have a record of the actual start/finish times if maybe you wanted to
do other calculations in the future ..........

=IF(AND(D$1>=$B2,D$1<=$C2),"X","")

(small workbook available if you want it emailed,.....
filename:XforHours.xls)
hth
Vaya con Dios,
Chuck, CABGx3

 

[Dennis Allen]

Thanks. I'll show it to the client and see if he wants to do it that
way...Dennis

Hi Dennis...........

Sample workbook sent by email.........hope it helps.

Vaya con Dios,
Chuck, CABGx3

Well, perhaps we'll convert the seven sheets into a database at some point.
Right now I just want to stick with the client request.

On a given sheet, each row is an employee. We need a button on each row.
This button activate a function. This function would activate a dialog box,
prompting the user for a start time, end time, and a break time. For

our

16
columns, we would fill any column within our time range with [X], the break
column would get a , the rest would be blank.

I'm not sure how I could translate a time 99:99 prompt. If the button

is

on
column C, 9:30 = column D, 10:00 = column E, etc. I imagine each button
would activate the function, feeding it the current col/row. By placing a
column of buttons before our set of 16 columns, the same function could
serve all rows and even all sheets.

If you have a sample, please email to it me (remove the XXX)
(e-mail address removed)

would in,
but and
all, way
you wanted
to

 

[Dennis Allen]

Hi again. Gave the client the file. He likes it! Thanks.

He's going to call back next week, but my guess is that he will want to
create an employee sheet. Adding an employee id column is not a problem.
The employee sheet will have to search all the other sheets (monday,
tuesday, wednesday, etc.), searching for a particular employee. Adding up
his [X]. Would I use sumif()?

Hi Dennis...........

Sample workbook sent by email.........hope it helps.

Vaya con Dios,
Chuck, CABGx3

Well, perhaps we'll convert the seven sheets into a database at some point.
Right now I just want to stick with the client request.

On a given sheet, each row is an employee. We need a button on each row.
This button activate a function. This function would activate a dialog box,
prompting the user for a start time, end time, and a break time. For

our

16
columns, we would fill any column within our time range with [X], the break
column would get a , the rest would be blank.

I'm not sure how I could translate a time 99:99 prompt. If the button

is

on
column C, 9:30 = column D, 10:00 = column E, etc. I imagine each button
would activate the function, feeding it the current col/row. By placing a
column of buttons before our set of 16 columns, the same function could
serve all rows and even all sheets.

If you have a sample, please email to it me (remove the XXX)
(e-mail address removed)

would in,
but and
all, way
you wanted
to

 

[CLR]

Hi Dennis............

I would still recommend doing it all on one sheet/database, and forgetting
about the Monday, Tuesday, Wednesday sheets. You can create those on demand
with the AutoFilter, as well as the "Employee" sheets/reports. All of the
filtering you're talking about would be easy with the AutoFilter or other
features. I would of course add the column for the Employee Number, and
another for the Day-of-the-week, as well as the date, Department Number,
etc......again, all on one sheet.............

As for the adding up of the "X"'s, just make that another column on the same
sheet and use the COUNTA function across the columns of each row in that
range.......and maybe a "Subtotals" session at the end....

You can eventually run everything with macros, and also automatically Hide
any unwanted columns from the Reports, etc.

Vaya con Dios,
Chuck, CABGx3

Hi again. Gave the client the file. He likes it! Thanks.

He's going to call back next week, but my guess is that he will want to
create an employee sheet. Adding an employee id column is not a problem.
The employee sheet will have to search all the other sheets (monday,
tuesday, wednesday, etc.), searching for a particular employee. Adding up
his [X]. Would I use sumif()?

Hi Dennis...........

Sample workbook sent by email.........hope it helps.

Vaya con Dios,
Chuck, CABGx3

Well, perhaps we'll convert the seven sheets into a database at some point.
Right now I just want to stick with the client request.

On a given sheet, each row is an employee. We need a button on each row.
This button activate a function. This function would activate a dialog box,
prompting the user for a start time, end time, and a break time. For

our

16
columns, we would fill any column within our time range with [X], the break
column would get a , the rest would be blank.

I'm not sure how I could translate a time 99:99 prompt. If the button

is

on
column C, 9:30 = column D, 10:00 = column E, etc. I imagine each button
would activate the function, feeding it the current col/row. By

placing

a Database
on department,
and just
put put
your