how do i create a daily income report (i'm a waitress)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to create a daily income report. I'm a waitress and it's hard to keep
track of $$$. Just something where I can calculate how much I make at the end
of the month.
 
Try this set-up

In Sheet2
----------
Enter in A1: Jan-2005
Copy A1 down to A60
(this returns a 5 year's worth
of monthly listing: Jan-2005, Feb-2005 .. Dec-2009)

Click Insert > Name > Define
Under Names in workbook, put: MyMonth
In the Refers to box, put: =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))
(This creates a dynamic range for use in the data validation we're going to
put in Sheet1)

In Sheet1
----------
Put in A2: 01-Jan-2005
Copy A2 down to say, A2000
(A2:A2000 will return the daily dates)

In the adjacent range B2:B2000 will be where you enter the daily income
(Select col B and format as currency)

Let's create a data validation list to select the month-year of interest

Select A1
Click Data > Validation
Under Allow, select "List"
In the Source box, put: =MyMonth
Click OK

Put in B1:

=IF(A1="","",SUMPRODUCT((MONTH(A2:A2000)=MONTH(A1))*(YEAR(A2:A2000)=YEAR(A1)),B2:B2000))

B1 will return the total income for the month-year selected in the droplist
in A1
If you select: Jan-2005 in A1, B1 will return the total income for Jan-2005,
and so on
 
If you'd like a sample book with the set-up implemented,
just drop me a line at either:

demechanik <at>yahoo<dot>com
or
xdemechanik <at>yahoo<dot>com

(both valid)
 
Back
Top