CODEWITHSUNDEEP
excelexcel-formula
Tim Mullady
Tim Mullady

Reputation: 35

setting dynamic column reference in excel

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

Upvotes: 0

Views: 1730

Answers (1)

Jormund
Jormund

Reputation: 203

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.

Upvotes: 1

Related Questions