+ Reply to Thread
Results 1 to 4 of 4

Automating a monthly report

  1. #1
    Registered User
    Join Date
    01-22-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    23

    Automating a monthly report

    Hi, Looking for some guidance. I want to automate a monthly delivery/invoice report.
    Various chemicals are delivered to various locations during the month. I need to generate a total invoice for the month for the parent company.
    The delivery information is taken from the Master Data (cells highlighted in yellow) and moved to the Invoice Sheet where some calculations are done (hightlighted in blue). The number of deliveries varies from month to month.

    Please see attachment.

    any guidance would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Automating a monthly report

    Your profile says you're using Excel 2003, but you've attached a .xlsx format (2007 onwards) workbook. Please update your profile with the correct Excel version you use.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-25-2019
    Location
    London
    MS-Off Ver
    newest
    Posts
    1

    Re: Automating a monthly report

    Hello, after doing some research this is what I could find.
    First, you create one sheet in your workbook for the “raw data” aka the data fromX/IRI (with or without formatting). This sheet will be refreshed through your data link to X or IRI.

    Second, you create a separate sheet which will be your formatted report. And here’s the trick: this second sheet is all formulas, referencing the raw data sheet.
    Your first instinct might be to start reformatting the cells in this spreadsheet. Don’t do it!!! Most of that formatting can be lost when you refresh (i.e. download fresh data from IRI/Nielsen) next month or pull data for a different account or time period.

    Instead, make a new sheet that references the raw data sheet. In my example below, every single thing that might change is a formula. The segments, time period, account name, the numbers themselves, the graph, and even the highlighting are all formulas.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Automating a monthly report

    @jpr1234 Hello and Welcome to Excel Forum. Your post indicates that there should be an attachment. To attach a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to extratc data as weekly report and monthly report
    By Nisar.mohammed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2014, 11:19 AM
  2. Automating Monthly Cashflows From Rent Roll
    By sah713 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2014, 02:00 PM
  3. Monthly, Bi-monthly, Quarterly, Yearly Report Tracking Help
    By eugene_lys in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-26-2014, 11:08 PM
  4. Replies: 1
    Last Post: 08-15-2013, 02:15 PM
  5. Abstracting a monthly report into yearly report
    By Deepa Shrestha in forum Excel General
    Replies: 1
    Last Post: 07-25-2013, 07:09 AM
  6. How to collect daily report from spread sheet and accumulate for monthly report
    By yshguru in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2013, 05:17 PM
  7. Any way of automating monthly updates?
    By friend11_6 in forum Excel General
    Replies: 7
    Last Post: 07-27-2008, 01:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1