Reference Problem w/ inserting rows

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

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

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


Thanks for any help

Paul

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.

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)

Herbert -
That worked well. Thank you.

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

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"dvtapse" <dvtapse.28cw2z_1148548501.9381@excelforum-nospam.com> wrote in
message news:dvtapse.28cw2z_1148548501.9381@excelforum-nospam.com...
>
> 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
>
>
> --
> dvtapse
> ------------------------------------------------------------------------
> dvtapse's Profile:
> http://www.excelforum.com/member.php...o&userid=30735
> View this thread: http://www.excelforum.com/showthread...hreadid=474314
>