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: 9655
Experience:  Bachelor of Science - Engineering Consultant
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

Customer Question

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.

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  John D replied 1 year ago.

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 1 year ago.

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


 


Thank you

Expert:  John D replied 1 year ago.
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 1 year 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.


 


 

Expert:  John D replied 1 year ago.

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 1 year ago.

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

Expert:  John D replied 1 year ago.
Great!

Will do. One moment please...
Expert:  John D replied 1 year ago.

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 1 year 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.

Expert:  John D replied 1 year ago.
Sure.

You can go ahead and rate while I make the changes

Thanks
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 1 year ago.

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 1 year 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.

Expert:  John D replied 1 year ago.
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 1 year 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?

Expert:  John D replied 1 year ago.
Ok five me a few minutes to prepare the instructions...

Customer: replied 1 year ago.


awesome. thank you so much.

Expert:  John D replied 1 year ago.
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 1 year ago.


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

Expert:  John D replied 1 year ago.
go for it :)
Customer: replied 1 year 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.

Expert:  John D replied 1 year ago.
Ok

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional