How can you dynamically change an Excel table column reference?

%3CLINGO-SUB%20id%3D%22lingo-sub-2523940%22%20slang%3D%22en-US%22%3EHow%20can%20you%20dynamically%20change%20an%20Excel%20table%20column%20reference%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2523940%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20multiple%20Excel%20tables%20storing%20numeric%20values.%20I%20am%20summarizing%20a%20rows%20values%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3D%2BSUM(Query_Current_Year%5B%40%5BBegin_Balance%5D%3A%5BBalance_Period6%5D%5D)%3C%2FP%3E%3CP%3EEvery%20month%2C%20I%20need%20to%20update%20each%20table%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3D%2BSUM(Query_Current_Year%5B%40%5BBegin_Balance%5D%3A%5BBalance_Period7%5D%5D)%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20store%20the%20current%20column%20string%20in%20a%20cell%20like%20A1%20with%20the%20value%20of%20%22Balance_Period6%22%2C%20then%20reference%20cell%20A1%20as%20a%20table%20column%3F%20Each%20month%2C%20I%20would%20only%20need%20to%20update%20one%20field.%3C%2FP%3E%3CP%3EI%20can%20think%20of%20numerous%20reasons%20to%20need%20this%2C%20so%20I%20am%20certain%20there%20is%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2523940%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2524000%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20you%20dynamically%20change%20an%20Excel%20table%20column%20reference%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1097841%22%20target%3D%22_blank%22%3E%40deragsdale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20use%20formula%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20430px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294060i56389D3BCE62E0C2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20sums%20all%20columns%20except%20last%20one%2C%20no%20changes%20are%20required%20if%20another%20column%20is%20added%2Fremoved%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

1 Reply

@deragsdale 

As variant you may use formula like this

image.png

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