I'm trying to automate my accounts in relation to the small bit of freelance work I do.
I have set up an Excel spreadsheet which is currently made up of 13 worksheets. These show each month (Jan thru Dec) and a Totals sheet which takes data from each of the other 12 worksheets to give a running total of income and expenses etc.
I currently use Word to create and send invoices to my clients. I then manually copy the data from the invoice into the relevant worksheet (Jan - Dec) dependant on when I carried out the work. In turn the Totals worksheet is automatically updated.
So far, all of the above I have been able to implement with no problems. However, I am now stuck.
The other day I was wondering whether it would be possible to add another worksheet to my spreadsheet which I could call Invoices and this be set up in the form of an invoice which I could just print out to send to clients as well as take the data on this invoice and use it to update the relevant cells within my other worksheets etc.
What I'd like to do is type out an invoice, using the Invoice worksheet then, once I've input all the relevant data (clients name, date of work, professional fees, amount for travel etc.), get Excel to read the date on the invoice, copy the data to the relevant worksheet (Jan thru Dec) and delete the data on the Invoice worksheet so it is "reset" back to being blank for the next time I go to use it.
This task is made slightly harder becuase I'd like the data copied from the Invoices worksheet to appear in a column going down the relevant date worksheet. Problem being is I never know how much work I will get each month. May be 1 contract may be up to 6. (see below for an example "sort of")
DATE WORKSHEET
January 2009
Date Client Invoice Total Travel Exp
20/01/09 Mr White £200.00 £30.00
22/01/09 Mr Black £200.00 £34.00
Total : £400.00 £64.00
DATE WORKSHEET
Feb 2009
Date Client Invoice Total Travel Exp
03/02/09 Mr Green £220.00 £30.00
09/02/09 Mr Black £200.00 £34.00
11/02/09 Mr Purple £250.00 £33.20
17/02/09 Mr Yellow £200.00 £20.00
Total £870.00 £117.00
TOTAL WORKSHEET
Fees Travel Expenses Profit/Loss
£1270.00 £200.00 £600.00 etc
INVOICE WORKSHEET
19/02/09 Mr Brown £200.00 £20.00
Press here to copy data!
Data from Invoice worksheet is taken and moved to Feb worksheet (Excel sees the date on the invoice as 19th Feb). Finds the next empty cell in the Feb worksheet (in this case underneath 17/02 Invoice for Mr Yellow) and copies the data into. Once this is done all data on the Invoice worksheet is deleted so it is blank ready for the next time I use it.
Although what I'm trying to do is complex, I don't think it is so difficult it cannot be done.
Can anyone help me before I go mad trying to work it out?
Many thanks in advance. Fingers crossed!
Hussman.
PS : Money for a pint, sent via Paypal, for anyone who can solve this to my complete satisfaction!
I have set up an Excel spreadsheet which is currently made up of 13 worksheets. These show each month (Jan thru Dec) and a Totals sheet which takes data from each of the other 12 worksheets to give a running total of income and expenses etc.
I currently use Word to create and send invoices to my clients. I then manually copy the data from the invoice into the relevant worksheet (Jan - Dec) dependant on when I carried out the work. In turn the Totals worksheet is automatically updated.
So far, all of the above I have been able to implement with no problems. However, I am now stuck.
The other day I was wondering whether it would be possible to add another worksheet to my spreadsheet which I could call Invoices and this be set up in the form of an invoice which I could just print out to send to clients as well as take the data on this invoice and use it to update the relevant cells within my other worksheets etc.
What I'd like to do is type out an invoice, using the Invoice worksheet then, once I've input all the relevant data (clients name, date of work, professional fees, amount for travel etc.), get Excel to read the date on the invoice, copy the data to the relevant worksheet (Jan thru Dec) and delete the data on the Invoice worksheet so it is "reset" back to being blank for the next time I go to use it.
This task is made slightly harder becuase I'd like the data copied from the Invoices worksheet to appear in a column going down the relevant date worksheet. Problem being is I never know how much work I will get each month. May be 1 contract may be up to 6. (see below for an example "sort of")
DATE WORKSHEET
January 2009
Date Client Invoice Total Travel Exp
20/01/09 Mr White £200.00 £30.00
22/01/09 Mr Black £200.00 £34.00
Total : £400.00 £64.00
DATE WORKSHEET
Feb 2009
Date Client Invoice Total Travel Exp
03/02/09 Mr Green £220.00 £30.00
09/02/09 Mr Black £200.00 £34.00
11/02/09 Mr Purple £250.00 £33.20
17/02/09 Mr Yellow £200.00 £20.00
Total £870.00 £117.00
TOTAL WORKSHEET
Fees Travel Expenses Profit/Loss
£1270.00 £200.00 £600.00 etc
INVOICE WORKSHEET
19/02/09 Mr Brown £200.00 £20.00
Press here to copy data!
Data from Invoice worksheet is taken and moved to Feb worksheet (Excel sees the date on the invoice as 19th Feb). Finds the next empty cell in the Feb worksheet (in this case underneath 17/02 Invoice for Mr Yellow) and copies the data into. Once this is done all data on the Invoice worksheet is deleted so it is blank ready for the next time I use it.
Although what I'm trying to do is complex, I don't think it is so difficult it cannot be done.
Can anyone help me before I go mad trying to work it out?
Many thanks in advance. Fingers crossed!
Hussman.
PS : Money for a pint, sent via Paypal, for anyone who can solve this to my complete satisfaction!
