D
Dan Wilson
Good day. I am using Excel 2002 and consider myself to be
an above average user. However, I have encountered a
problem that I cannot fix on my own.
I run a retail store and keep track of my daily and
monthly totals using a workbook containing 31 worksheets,
one for each day of the month. There is also a worksheet
containing totals for the month. On each worksheet I
track the names of employees who worked on that day. I
want now to track the cash over/under result for each
employee of each shift.
I have created a table on each of the daily worksheets to
list the employees who worked, the shift they worked and
the over/under amount for that employee for their shift.
I have created a separate worksheet to track this data
over an entire month. There is a row for each employee
and in each row there are columns for the employee name,
31 columns for the days of the month and a total column to
report the total over/under for the employee for the month.
EX:
A 1 2 3 .. 31 Total
J. Smith .25 -1.00 .75
From the example above, I am using the following formula
=IF(VLOOKUP($A5,Day1!$I$41:$M$51,5,FALSE)="",0,(VLOOKUP
($A5,Day1!$I$41:$M$51,5,FALSE)))
Cell A5 is the employee name
Cells I41:M51 are the table containing the employee name,
the over/under amount and the shift worked.
Position 5 is the column containing the over/under amount
in cells I41:M51.
The problem is - not all of the employees will work on a
given day. Thus, the results of my VLOOKUP are #N/A for
an employee who did not work on that day. This would not
be a problem except that when I try to total a given day
or total the over/under for a given employee for the
month, the SUM also returns the #N/A error.
If there is another way to accomplish my goal, I am open
to changing the formula or the process. Any help is
appreciated. Direct answers via email will also be
appreciated. If any more details are needed, let me know.
Thanks, Danno...
an above average user. However, I have encountered a
problem that I cannot fix on my own.
I run a retail store and keep track of my daily and
monthly totals using a workbook containing 31 worksheets,
one for each day of the month. There is also a worksheet
containing totals for the month. On each worksheet I
track the names of employees who worked on that day. I
want now to track the cash over/under result for each
employee of each shift.
I have created a table on each of the daily worksheets to
list the employees who worked, the shift they worked and
the over/under amount for that employee for their shift.
I have created a separate worksheet to track this data
over an entire month. There is a row for each employee
and in each row there are columns for the employee name,
31 columns for the days of the month and a total column to
report the total over/under for the employee for the month.
EX:
A 1 2 3 .. 31 Total
J. Smith .25 -1.00 .75
From the example above, I am using the following formula
=IF(VLOOKUP($A5,Day1!$I$41:$M$51,5,FALSE)="",0,(VLOOKUP
($A5,Day1!$I$41:$M$51,5,FALSE)))
Cell A5 is the employee name
Cells I41:M51 are the table containing the employee name,
the over/under amount and the shift worked.
Position 5 is the column containing the over/under amount
in cells I41:M51.
The problem is - not all of the employees will work on a
given day. Thus, the results of my VLOOKUP are #N/A for
an employee who did not work on that day. This would not
be a problem except that when I try to total a given day
or total the over/under for a given employee for the
month, the SUM also returns the #N/A error.
If there is another way to accomplish my goal, I am open
to changing the formula or the process. Any help is
appreciated. Direct answers via email will also be
appreciated. If any more details are needed, let me know.
Thanks, Danno...