Linked Cells

[VBA]

Heya ,

I've got an excel sheet to which I add data (whole columns) every once in a while. My first column (A) states the name of the data, my second (B) the unit and my third (C) the average value of the data in the columns behind it, each containing the data for a specific date. I hope this is understandable

My question is this:
How do I make sure column C always takes the average value (which is {=Sum(D:AA)/COUNTA(D:AA)} right now) without me having to change the last column all the time? Thus if I add a column AB, that this one will be included - or that a warning will turn up stating something like "you added a column outside the average formula".
I know I can make the formula go to column ZZ or such ... but I still there will be a day when I reach the limit... I don't add the new data to the end of the sheet, but "insert" it at the D column - so I won't see it when I reach it...

And an other question:
If I link a cell on one worksheet to a cell in another worksheet by the means "=Sheet2!B9", what exactly happens to that link when I insert rows or columns? How can I make sure that no matter what I do, it will stay linked to that particular value that was in cell B9 of Sheet2 before I started inserting rows and columns there (maybe causing the value of B9 to change, in which case I'd like the changed value to be taken over by the other worksheet).

Any help would be appreciated.

Greetings

 

[xenou]

Maybe using Offset will give you the range you need?

Here I assume that all columns in Cells D to the last column have a column header (no blanks) - so the count of columns in the range is correct.

OFFSET(C1,0,1,65536,COUNTA(D1:IV1))

Or, in the formula altogether:
=SUM(OFFSET(C1,0,1,65536,COUNTA(D1:IV1)))/COUNTA(OFFSET(C1,0,1,65536,COUNTA(D1:IV1)))

Does that work? Note: I have IV as the last column and 65536 as last row b/c using excel 2003
---------------------------------------------------------------------

Regarding your second question, usually excel is pretty good at keeping up with these things, at least if you're all in the same workbook. But, you can also use a named range which can in some cases be better:

=MyNamedRange instead of =Sheet2!A1

Alex

 

[VBA]

Thank you.

I implemented the offset in my code, and it seems to be working I didn't know that option existed, thanks for telling.

And I'm using names for those particular cells now. I'm not sure whether it really matters, but it's better to be safe than sorry

Take care.