Good day,
We are in search for assistance in automating and improving an existing expense tracking Excel sheet in Microsoft Excel using VB and Macros.
See the excel sheet attachment
Three Implementation tasks:
=======================
Task 1: Button to start new month expenses
-------------------------------------------------------
• Problem: Since the current sheet uses formula, each time a new month is started or some of the parameters (on data sheet or per-diems sheets) are altered, the formula recalculate. This is unwanted.
• Suggested Solution: Data for old months should not be re-calculated once the user has started submitting expenses for a new month. Therefore, at the start of a new expense period, data for the old period needs to be "frozen", so that new changes do not affect already submitted data.
• In addition, at the start of each month, the entire current Expense-Details sheet must be copied to Expense-Details-old sheet. In case of errors, the user can manually restore the old state. This means the Expense-Details-old sheet is completely dropped and replaced with a new version at the start of each month.
• Steps: The user fills in the data for current month and year in the Expense-Details sheet and clicks on a button. The sheet is prepared as described above. He then proceeds to
Task 2 - Implement a Button to create summary sheet
-------------------------------------------------------------------
• Delete all expense data in the Expense-Summary sheet, except for the header, footers, and formating
• Copy data from relevant columns from Expense-Details sheet to the Expense-Summary Sheet for the current month only as values only
• Calculate the sums at the bottom as in the current sheet
• Add the additional fields and text at the bottom
Task 3 Correct calculation of Per-diems (column J to R) based on the per-diems sheet
------------------------------------------------------------------------------------------------------------
• Current calculation uses formulae that take into account only the country and the time spent out of home town.
• Refer to comments on the per_diems sheet and include the city in the calculation of the per diems.
Note:
• Reciept ID:
• Expenses of type P_Per_Diems do not have any receipt ID => use "-- # --"
• All other expenses are number increasingly using the following information
a. A: Personnel Nr => Cell C5
b. B: Last 2 digits of current year => Cell C6
c. C: Expense Month in 2 digits => column E
d. D: Receipt number increasing from 1 at the start of each month for all receipts of that month in 2 digits
e. Result is the concatenation of A-BC-D => See excel sheet for example
Budget available is less than 50 USD. If your bid is more than this amount, do not even bother.
Regards,