+ Reply to Thread
Results 1 to 6 of 6

Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

  1. #1
    Registered User
    Join Date
    05-07-2016
    Location
    Virginia
    MS-Off Ver
    MS Office 360 (Mac)
    Posts
    3

    Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    How can I change this "Annual" Financial Report to a "Monthly" Financial Report without loosing the current functionality. I want to be able to use months (January, February, etc) instead of years (2016, 2015), but when I try to do so the functionality is lost. I'm having problems with the calculation tab, where all the data is being drawn from. When I try to use months, it functionality is lost. Is this just not possible? I have been trying for days and I am not able to do so without loosing the functionality.

    Any help would be appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,657

    Re: Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    I can't see any "monthly" data (dates) to permit monthly reporting.

    How exactly are you changing it?

    Most if not all calculations are "lookups" for a given year. If yearly data is replaced by monthly data, and selection is on month, rather than year (or both) it should work.
    Last edited by JohnTopley; 05-07-2016 at 10:26 AM.

  3. #3
    Registered User
    Join Date
    05-07-2016
    Location
    Virginia
    MS-Off Ver
    MS Office 360 (Mac)
    Posts
    3

    Re: Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    On the "Financial Data Input" tab, I tried to replace the years with months and most of the functionality was lost. It will return the value for that current month, but not the previous month. All data concerning the previous month shows blank or "N/A" if not using years. Without the returning the data from the previous period/month, most of the functionality is lost.

    I tried to change the "Calculations" tab so it can work with text data but it looks like the the formulas are based on numbers, it does not recognize text data. For example, it uses a numbered position to return a value that feeds to the ket metrics section. That position is generated based on a year. For some reason, replacing it with a month does not return the same results.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,657

    Re: Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    See attached.

    Changed year headings to months and highlighted changed formulae.

    See if this works for you.

    I will detail formulae changes if all is OK.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-07-2016
    Location
    Virginia
    MS-Off Ver
    MS Office 360 (Mac)
    Posts
    3

    Re: Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    WoW, that's exactly what I was trying to do. Yes, if you could explain how that was done that would be great!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,657

    Re: Change from "Annual" to "Monthly" report w/o loosing functionality... Help!

    Named range "lstYears" contains months "Jan" to "Dec". NOTE range needs to be adjusted to $N$3 rather than $I$3

    In Calculations

    in D3: =MATCH(C3,lstYears,0) returns relative month number ( 1 to 12)

    in D4: =D3-1 i.e. previous month number

    in C4: =INDEX(lstYears,,D4) previous month from C3

    in F6 to C6: =MAX(G6-1,0) returns relative month numbers from G6: if month number is <= 0 then set to 0.

    in C8 (copied across and down) =IF(C$6<=0,NA(),(INDEX('Financial Data Input'!$C$4:$I$18,$A8,C$6))) returns data from selected month. If month <=0 then set to "#N/A"

    in c15: (copied across and down) =IF($B15="",NA(),IF(C$6<=0,NA(),(INDEX('Financial Data Input'!$C$4:$I$28,$A15,C$6)))) returns data from selected month. If month <=0 then set to "#N/A"

    in Financial Report:

    L2: List of Months

    F15: ="LAST MONTH (" & INDEX(lstYears,,MATCH(SelectedYear,lstYears,0)-1) & ")" returns "Previous month" from Match of entry in E15


    I think that's it!!!

+ 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. If "6343" in cell A3 matches "monthly" in B3 show a "Y" in C3
    By laaxy in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-05-2014, 07:38 PM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. Change second "|" sings into "" , third "|" into "" and fourth into ""...
    By wali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2008, 06:31 AM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 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