Merge column index with a cell reference

[Hal9va]

Hi I am trying to use an Average function but I am a bit confused with the syntaxis.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=AVERAGE(D2:D & 'C2')
<o> </o>
Cell c2 has a numeric value, which I want to put next to D. Say range d2:d5, but instead of 5 I want to put a reference to cell c2, which returns the value of 5. So whenever c2 changes Average also changes. Thanks a lot in advance.

 

[AlphaFrog]

Try using the INDIRECT function

=AVERAGE(INDIRECT("D2:D" & C2))


Or The OFFSET function...

=AVERAGE(OFFSET(D2,0,0,C2-2))

 

[Hal9va]

Thanks a lot AlphaFrog! it really helps!