How to reference cells dynamically

[Guest]

There're some times when I want to reference the cell dynamically in a
function: avg(A1the cell not known or changes)) I would like to achieve
something like this: avg(A1:address(R1C1)) or avg(A1:F(address(R1C1)), which
doesn't of course work, when the address function or some other borrows the
cell reference from the value of another cell. Can I achieve this result
without using a macro?

 

[Guest]

Yes:

Checkout the INDIRECT() function in Help.

 

[Guest]

the indirect function and named ranges are very useul here