Toggle Formula Reference

    Hi all -


    I have a sheet in my workbook with at least 180 small tables, there may be more.
    I woulds like to be able to change total formulas for all tables at once to show either year-to-date or total year.


    For example:
    If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.


    Attached small sample of one of the tables
    Thanks much.


    Best regards,
    -marc

    Re: Toggle Total Formula Between Year To Date And Total Year


    Hi marc,


    best solution I could think of.


    I assume you have a rolling 12 month history and that :


    Year to Date is the total of the current year months
    Total Year is the total of the rolling 12 month fiscal year


    If so then:


    Put this formula in cell F18 and copy to cell G18 and in the same position for all other tables.


    =SUM(INDIRECT((CHAR(COLUMN(F17)+64)&ROW(OFFSET(F17,-$A$1,0)))):F16)


    Now in cell A1 enter the number 12 for Total Year or enter the number of current year months. This will toggle all tables.


    See attachment

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Toggle Total Formula Between Year To Date And Total Year


    Here is one very simply approach to consider:


    Create a cell with data validation setup to choose the viewing preference (YTD, Period1, Period2, etc)


    Then, in the cell(s) containing the total, use a nested IF statement referencing the viewing preference selected in the validation cell. This, obviously, assumes you have created separate formulas for the calculation of totals for each viewing period. The correct total will display when the condition tested is true for the selected preference.


    HTH,


    AAE

    Re: Toggle Total Formula Between Year To Date And Total Year


    Thanks guys!
    Bill,


    I think you are on the right track
    The formula doesn't seem to return the expected result



    For example, enter 2 in cell A1
    The desired result in F18 is 146.2


    It appears you are working upwards from the total rather downwards from the start of the Fiscal Year. The notation in Col E for the periods with the start at October


    Is it an easy fix to alter the formula to start at F5 and work downward based on the selection in $A$1?


    Thanks again.
    Best regards,
    -markc

    Re: Toggle Total Formula Between Year To Date And Total Year


    Thanks AAE -


    Looks like your formula defines a period as a quarter
    However, there are 12 periods in a year, not truly a month (4-4-5 calendar)
    Also your formula does not allow for YTD status if in-between the quarters.


    What if through period 5 (February)
    The total should include Period 1 (October) thru Period 5 (February)


    I don't think Nested IF statments will work
    Even if they would, that would be one brutal formula.


    Thanks
    Best regards,
    -markc

    Re: Toggle Total Formula Between Year To Date And Total Year


    You didn't define what a "period" is, so I made an incorrect assumption. Since a period is not a quarter, a nested IF formula is definitely not the way to go. Sorry.

    Re: Toggle Total Formula Between Year To Date And Total Year


    Thanks for your help
    Sorry my description of the problem didn't lay it out better


    Best regards,
    -markc

    Re: Toggle Total Formula Between Year To Date And Total Year


    Hello markc,


    Try this sum formula in F18


    =SUM(F5:INDEX(F5:F16,$A$1))


    If you put 2 in A1 it will sum up to period 2, same for all other periods. If A1 is 0 (or 12) it'll sum the whole column

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login