How can you dynamically change an Excel table column reference?

[deragsdale]

I have multiple Excel tables storing numeric values. I am summarizing a rows values like this:

    =+SUM(Query_Current_Year[@[Begin_Balance]:[Balance_Period6]])

Every month, I need to update each table like this:

    =+SUM(Query_Current_Year[@[Begin_Balance]:[Balance_Period7]])

Is there a way to store the current column string in a cell like A1 with the value of "Balance_Period6", then reference cell A1 as a table column? Each month, I would only need to update one field.

I can think of numerous reasons to need this, so I am certain there is a solution.

[#2]

@deragsdale 

As variant you may use formula like this

it sums all columns except last one, no changes are required if another column is added/removed