How to display dynamic range

[Don]

Sometimes I have to troubleshoot a formula that uses dynamic ranges. Here is
an example formula:

=CORREL(OFFSET(Sheet3!$A$1,1,MATCH(C$1,Sheet3!$1:$1,0)-1,$A$1),(OFFSET(Sheet3!$A$1,1,MATCH($A241,Sheet3!$1:$1,0)-1,$A$1))).

Is there a way to determine the exact cells addresses that the correl
function will operate on?

For example, the Formula Palette for the OFFSET function above may say:
Ref=0, row=102, col=214, height =144. Is there a way to determine the exact
cell address range this represents?

Thanks.

 

[Dave Peterson]

I'd create a named range for each of them.

Then I could just type in the name into the namebox and hit enter and have the
range selected.

Debra Dalgleish has some notes here that may help:
http://contextures.com/xlNames01.html#Dynamic

 

[T. Valko]

What version of Excel are you using?

In Excel versions 2002 and above you can use the formula auditing tools to
see exactly what your formula is doing.

Select the cell that contains the formula.

In Excel 2007:

Formulas tab>Formula Auditing>Evaluate formula

In Excel 2002,2003:

Tools>Formula Auditing>Evaluate formula

Note that in versions 2002 and 2003 using evaluate formula on certain types
of "complex" array formulas will cause Excel to crash! Your formula will
evaluate just fine.

 

[Don]

What version of Excel are you using?

In Excel versions 2002 and above you can use the formula auditing tools to
see exactly what your formula is doing.

Select the cell that contains the formula.

In Excel 2007:

Formulas tab>Formula Auditing>Evaluate formula

In Excel 2002,2003:

Tools>Formula Auditing>Evaluate formula

Note that in versions 2002 and 2003 using evaluate formula on certain
types of "complex" array formulas will cause Excel to crash! Your formula
will evaluate just fine.

 

[Don]

I'm using Excel 2000. Looks like a good reason to upgrade. I guess an
upgrade to 2004 is the minimum to avoid the crash to mention.
Thanks,
Don

 

[Don]

Hey, I tried that and it seems very promising.

Although I am having a strange issue where the definition changes after I
type the name into the name box. After redefining the name a couple of times
it finally seems to stick and then your suggestion works great. I must be
doing something wrong to cause the definition to change. Anyway, if even if
I have to re-define the name a couple of times it's worth it.

Thanks.