CODEWITHSUNDEEP
excelexcel-formula
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

Excel Offset formula in a cell by itself

This phrase is embedded in many formulas on my sheet:

OFFSET(Table1[ReportDate],0,$B$1)

It returns a reference to a column in Table1.

To make those many formulas shorter, I'd like to extract this OFFSET formula to a separate cell for the others to refer to.

The OFFSET returns a reference. Putting OFFSET(…) in a cell just returns #VALUE, and so does INDIRECT(OFFSET(…)).

EDIT: The "many formulas" are SUMIFS, and the OFFSET chooses the column to be summed:

=SUMIFS(OFFSET(Table1[ReportDate],0,$B$1),Table1[ColumnB],$H10,Table1[Report Date],"<="&rYesterday)

Upvotes: 0

Views: 656

Answers (1)

Terry W
Terry W

Reputation: 3257

If I understand you correctly, you want to replace the original OFFSET formula with something simpler to be used in other formulas.

You can do so by giving a name to this OFFSET formula.

Solution

In the above mock-up example, I have given a name SUM_Rng for the OFFSET formula and used it in my second formula, which is the same as your original SUMIFS formula.

You can press Ctrl+F3 to bring out the Name Manager in Excel and add or modify names which can represent a reference of a cell or a range either hard-coded or returned by a formula. I noticed that you already used a name rYesterday in your SUMIFS formula so there should be no problem for you to add this formula to the name manager.

Cheers :)

Upvotes: 2

Related Questions