# 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

## Files

• 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

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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

Here's a sample using the approach I mentioned.

AAE

## Files

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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

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

Mark, you should have 1 table on one Worksheet and from that you can get your umpteen seperate tables VERY easily.

• Re: Toggle Formula Reference

Thanks Daddylonglegs,

Works great!

Best regards,
-markc

## Participate now!

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