Excel Offset formula in a cell by itself

0
votes

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)
1
excelexcel-formula
can you show how it is being used in other formula. It should return an array of values. – Scott Craner
It is returning a column in the table, an array of values. This cannot be contained in a single cell. But you can replace them with the following non-volatile INDEX: INDEX(Table1,0,MATCH("ReportDate",Table1[#Headers],0)+$B$1)) – Scott Craner
to remove it you would need to put the address of the reference, but then you would end up using INDIRECT. Both Address and Indirect are volatile and if used too many time will slow the calcs down as they would need to be recalced every time Excel recalcs. – Scott Craner

1 Answers

2
votes

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 :)