Reference Problem w/ inserting rows

[Paul987]

I need to find the average of a range of cells, A7:A11. However, I wil
insert a row in front of A7, causing the original A7 to become A8.
still want the formula to refernce A7:A11, and just drop the cell tha
got pushed out. However...

Regardless of the static symbol($), my reference is still moving t
A8:A12, following the original group of numbers.

I thought the static should stop this, but it doesn't.


Thanks for any help

Pau

 

[Dave O]

The only workarounds I can think of are:
~ After you insert the row, trigger some code that rewrites the
formula. The insert and the rewrite could be combined into one
routine, and attached to a button on your sheet.
~ Before you insert the new row, convert the AVERAGE formula to a
static text string by replacing the equal sign in the formula with your
initials, or some random string of letters. After the insert, search
for the random string of letters and replace with the = sign.

 

[Herbert Seidenberg]

Select A1 through last data row
Insert > Name > Define > MyData
Also define this name: Sel_7_11
Refers To:
=OFFSET(MyData,6,0,5) or
=INDEX(MyData,7):INDEX(MyData,11)
Then get the average with
=AVERAGE(Sel_7_11)

 

[Paul987]

Herbert -
That worked well. Thank you

 

[dvtapse]

Hello

Suppose i have protected the file with passward. It could not be
opened, but it will be delted by anyone. So anybody help me how to
protect the file which could not be delted without passward.

Deepak Tapse

 

[Arvi Laanemets]

Hi

In WindowsXP, you can determine user rights for Folder/File, where you can
allow/restrict editing, deleting, etc. for users. No password asking
of-course except when logging in to Windows.