How to check the range returned by Excel OFFSET function?

I have the following excel function:

OFFSET(Foglio1!C1;S_1x-1;0;E_1x-S_1x+1;1)

If I want to check the returned range during debugging, say e.g. D6:F10, is there a way to explicitly show it?

E_1x and S_1x are user defined names of single cells in the formula above.

I have discovered some indirect ways to check it, but having a method that directly shows me "D6:F10" (in the example above) would be much more practical. Thanks for the attention.

Solution

You can use the following formula to show you the range of that OFFSET() formula in a cell:

=LET(
    initial,A1,
    row_shift,3,col_shift,4,
    height,5,width,8,
    range,ADDRESS(ROW(initial)+row_shift,COLUMN(initial)+col_shift)&":"&ADDRESS(ROW(initial)+row_shift+height,COLUMN(initial)+col_shift+width),
    range
)

using your starting range "Foglio1!C1" and variable names "S_1x" and "E_1x" that becomes:

=LET(
    initial,Foglio1!C1,
    row_shift,S_1x-1,col_shift,0,
    height,E_1x-S_1x+1,width,1,
    range,ADDRESS(ROW(initial)+row_shift,COLUMN(initial)+col_shift)&":"&ADDRESS(ROW(initial)+row_shift+height,COLUMN(initial)+col_shift+width),
    range
)

Note 1: Apparently we use different punctuation for formulas. Make sure to use semicolon ; instead of comma , for separation.

Note 2: For this to work, the initial range has to be a single cell and the variables/parameters cannot be error values.

Edit 1:

Without the use of the LET() function the formula can be written as follows, using your starting range "Foglio1!C1" and variable names "S_1x" and "E_1x":

=ADDRESS(ROW(Foglio1!C1)+(S_1x-1),COLUMN(Foglio1!C1)+(0))&":"&ADDRESS(ROW(Foglio1!C1)+(S_1x-1)+(E_1x-S_1x+1),COLUMN(Foglio1!C1)+(0)+(1))

This will show a range with absolute references, i.e. encapsulating the range letters and numbers with $, e.g. $A$2:$M$9.

Combine the above formula with the SUBSTITUTE() function to get rid of the $ symbol by replacing it with an empty string value ("")