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


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=474314

The only workarounds I can think of a
~ 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.


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=474314

Reference Problem w/ inserting rows
 

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

Reference Problem w/ inserting rows
 

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<attarkon.ee )


"dvtapse" wrote in
message ...

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