setting dynamic column reference in excel

Question

This formula is accomplishing my objective but I don't like hard coding the column references. Can someone help me make the formula reference the columns more dynamically? I've been trying to combine various functions without any luck. MATCH(), ADDRESS(), COLUMNS(), etc..

Index match works for lookups but I'm trying to sum data

=SUM(SUMIFS(DATA!$E:$E,DATA!$A:$A,"=ALL",DATA!$C:$C,{"Jan","Feb","Mar"},DATA!$B:$B,"="&ALL_Seasonality!$A$2))

The data is in the DATA!

column E header in row 3 - 2017 Pax

column A header in row 3 - Sorter

column C header in row 3 - Month

column B header in row 3 - SBU

Any help would be greatly appreciated.

Thanks,

Tim

1. on the formula.. sumifs() is sufficient.. you don't need sum() at the outside if you only have 1 element inside sum(). || 2. In ".. I don't like hard coding the column references." part, had you tried indirect() or offset() ? Using either one you may 'link' the references to other (text/number) cell values. — p._phidot_
The sum is needed because of the DATA!$C:$C,{"Jan","Feb","Mar"} condition. it's aggregating multiple months in the condition — Tim Mullady

Jormund Jormund · Accepted Answer · 2019-01-10T16:59:48

You could assign names to the ranges of interest (in your example the entire columns A, B, C and E in sheet DATA). Names can be assigned by selecting a range and entering the name into the name box (box on the left hand above the spreadsheet area which shows what is selected).

see https://exceljet.net/named-ranges

Instead of referencing the columns in your formula you would then just refer to the names. If you need to change your references you can use the "Name Manager" (Formulas - Name Manager) to change which column or sheet the range refers to.

If this is not what you are looking for could you please elaborate a little more on what you expect from the dynamic referencing.

setting dynamic column reference in excel

1 Answers