Average over a dynamic range of values in a different worksheet

Hi,

My problem is this:

I want to use the Range("E1"). Formula = "Average..." formula to calculate a dynamic average on another worksheet in the same workbook. The range will be determined by what users enter into a specific cell. And though the column of data (E) is the same it is the rows that change. I can do this easily using the formula:

Please Login or Register  to view this content.

However, when this runs it doesn't display the function in the cell on the worksheet and for transparency this is what I need.

Is this clear? I really hope someone can help as I'm pulling my hair out looking for solutions on the web.

Many thanks in advance.

Thanks YvorL

That's exactly what I wanted to do but couldn't figure out where all the &"" signs went. So thanks for that, I just have one more query.

Currently I have a line of code to autofill the average values acroos columns E-AC underneath this. However with the new line of code (as provided by you) it autofills the same value in all columns, as the dollar signs are on columns and rows. Do you know how to remove them from the columns? Or, will I need to loop through all columns to achieve this?

Code is:

Please Login or Register  to view this content.