How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

Excel expense report converted to data list

This answer was rated:

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

 

 

Customer: replied 4 years ago.

http://www.ge.tt/229iOjU/v/0?c


 


Thank you

Got it.

Could you explain in a bit more detail what exactly you are trying to do

There are 2 sheets. Which sheet do you want to replicate, (and which columns)

Where do you want the result to be placed
Customer: replied 4 years ago.

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.


 


 

Thanks

Ok have a look at this and let me know if we are on the right track

 

http://www.ge.tt/6QvUdjU/v/0?c

 

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

 

 

Customer: replied 4 years ago.

wow - this is awesome. i would like to add employee name, store number and project number.

Great!

Will do. One moment please...

Ok here you go

 

http://www.ge.tt/4ekSkjU/v/0?c

 

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

 

 

 

 

Customer: replied 4 years ago.


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.

Sure.

You can go ahead and rate while I make the changes

Thanks
John D and 6 other Microsoft Office Specialists are ready to help you

Thanks. Much appreciated :)

 

http://www.ge.tt/5gh6qjU/v/0?c

 

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

 

 

 

 

 

Customer: replied 4 years ago.


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.

You're welcome. Glad I could help

Yes just post me a message here when you are ready on Monday.

You will need to copy the code to each of the individual workbooks that you want to apply the macro on. When you have the files ready let me know and I will send you the step by step instructions for copying the macro

Have a great a weekend
Customer: replied 4 years ago.


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?

Ok five me a few minutes to prepare the instructions...

Customer: replied 4 years ago.


awesome. thank you so much.

Ok here you go

Copying the Macro (from module)
1- Close all Excel workbooks and Excel programs.
2 - Open the file that I sent you.
3 - Press the keys ALT + F11 to open the Visual Basic Editor
4 - Copy the macro code by selecting all the code lines and pressing the keys CTRL+C
5 - Close this file
6 - Open the other workbook that you want to copy the macro to
7 - Press the keys ALT + F11 to open the Visual Basic Editor
8 - Press the keys ALT + I to activate the Insert menu
9 - Press M to insert a Standard Module
10- Paste the code by pressing the keys CTRL+V
11- Press the keys ALT+Q to exit the Editor, and return to Excel
12- Save your excel file.

.

Creating a button for the macro
1- After copying the macro to the new workbook close excel and all files
2- Open the other workbook that you want to add the button in
3- In Excel 2007/2010 click the Developers tab > Insert icon - if you do not see the Developer tab on your Ribbon let me know)
4- Click the "button" icon (it should be second icon in the toolbox)
5- Click on the worksheet where you want to install the button
6- Drag your mouse diagonally to draw the button with the desired size
7- When the Assign Macro dialog comes up, select the macro in question (it is called "ExpenseReport_c"), and click OK.
8- Finally right click on the button and choose Edit Text to edit the name of the button.

Hope this helps
Customer: replied 4 years ago.


wow. i will give it a try now...wish me luck.

go for it :)
Customer: replied 4 years ago.


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.

Ok

Related Microsoft Office Questions