Microsoft Office Questions? Ask a IT Expert for Support ASAP
I have an Excel expense report that tracks cost by person/store/project # XXXXX several expense categories/codes horizontally. I need to replicate the data in a specific format with each cost in a vertical list.
I have a file for your review in a confidential/secure setting.
Hi. Welcome back!I will be happy to help you with this.Could you please upload your file here and then copy the link and post it here
The Test tab is where the data comes through horizontally - I receive individual files from employees filled out with the info - they are showing expenses by date by store visit.
I then use this info for two purposes -- one - to become a tally on the Expense Entry tab so that the costs are in a format so that we can see them by Cost Code and thus match to the accounting system
(On line 14 there are cost codes that align to the category names in the headings on line 15 - 310 is Airfare, 330 is hotel etc. and two - to be a data bank where I can put line numbers and pull the data over to a client report for billing purposes.
I am trying to streamline the process so that I don't have to have every piece of cost on every expense report retyped.
I did one example of how the costs have to be retyped into the Expense Entry tab. As long as I can link the data by line number to the final billing doc, the Expense Entry format could be flexible.
ThanksOk have a look at this and let me know if we are on the right track
To run the macro click the "Generate Expense Report" button on sheet1 (make sure that you have your macros enabled so the macro can run)
The macro first clears the 'Expense Entry' sheet then goes through the data in the Data sheet and pulls each cost code with all its values and places them vertically on the Expense Entry sheet
Of course we can add more columns as needed. Go ahead and test this version and let me know what adjustments/addition will be needed
wow - this is awesome. i would like to add employee name, store number and project number.
Ok here you go
Let me know if any other adjustments are needed
In the meantime thank you for rating the answer "Excellent" or "Good" if I was able to help
perfect - and amazing. now, since I have to publish the expense report to the employees - could we hide the button over to the right beyond column O? and change the name on the button to Generate Expense Entry Log? and if I have to rerrange the expense report or the expense entry log a little bit, will that affect the macro? thanks very much.
Thanks. Much appreciated :)
With regard to changing the columns, in general the macro will have to be modified if any of the effective columns gets moved. However you can send me the file with the new column arrangement as soon as you can and I will be happy to modify the code as part of this question
Thanks. I am working on this extensively this weekend, so I will know by Monday what changes are needed.
Since I have a lot of expense reports that don't have the macro in the file already, is there a way to put it in those files?
Really appreciate your help and expertise.
since i have to work all the expense reports this weekend, could you tell me how to install the macro in a different file now?
awesome. thank you so much.
wow. i will give it a try now...wish me luck.
ok - well I can follow your instructions, the problem is I didn't take into account that I have modified the expense report. macro works fine just doesn't have the right information to pull. ah well, this is why i need a programmer. thanks for your help. talk to you on monday.