Constant Cell Problem
 

I have a workbook in Excel 2007 with two worksheets in it Sheet1 and Sheet2.

On Sheet1 I have a reference to a row and cell on Sheet2 that I want to
remain constant.
That formula is: =Sheet1!$B$7 as the cell on Sheet1(with the formula) always
has to take its value from what is inserted in B7 on Sheet2. Each week a new
row on Row7 on Sheet2 has to be inserted. So I Copy Row, then Insert Copied
Row and start to replace some of the new figures in that row including B7.
When I go back to the Sheet1 - the formula above has changed to:
=Sheet1!$B$8

Basically I want the formula on Sheet1 ALWAYS to point to B7 on Sheet2 - how
do I do that?

Nick

Constant Cell Problem
 

You need =INDIRECT("Sheet1!B7")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Nick Wakeham" wrote in message
m...
I have a workbook in Excel 2007 with two worksheets in it Sheet1 and
Sheet2.

On Sheet1 I have a reference to a row and cell on Sheet2 that I want to
remain constant.
That formula is: =Sheet1!$B$7 as the cell on Sheet1(with the formula)
always has to take its value from what is inserted in B7 on Sheet2. Each
week a new row on Row7 on Sheet2 has to be inserted. So I Copy Row, then
Insert Copied Row and start to replace some of the new figures in that row
including B7. When I go back to the Sheet1 - the formula above has changed
to: =Sheet1!$B$8

Basically I want the formula on Sheet1 ALWAYS to point to B7 on Sheet2 -
how do I do that?

Nick

Constant Cell Problem
 

Use

=INDIRECT("Sheet1!$B$7")

instead of
=Sheet1!$B$7

"Nick Wakeham" wrote:

I have a workbook in Excel 2007 with two worksheets in it Sheet1 and Sheet2.

On Sheet1 I have a reference to a row and cell on Sheet2 that I want to
remain constant.
That formula is: =Sheet1!$B$7 as the cell on Sheet1(with the formula) always
has to take its value from what is inserted in B7 on Sheet2. Each week a new
row on Row7 on Sheet2 has to be inserted. So I Copy Row, then Insert Copied
Row and start to replace some of the new figures in that row including B7.
When I go back to the Sheet1 - the formula above has changed to:
=Sheet1!$B$8

Basically I want the formula on Sheet1 ALWAYS to point to B7 on Sheet2 - how
do I do that?

Nick

Constant Cell Problem
 

Many thanks for that - superb

Nick
"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Use

=INDIRECT("Sheet1!$B$7")

instead of
=Sheet1!$B$7

"Nick Wakeham" wrote:

I have a workbook in Excel 2007 with two worksheets in it Sheet1 and
Sheet2.

On Sheet1 I have a reference to a row and cell on Sheet2 that I want to
remain constant.
That formula is: =Sheet1!$B$7 as the cell on Sheet1(with the formula)
always
has to take its value from what is inserted in B7 on Sheet2. Each week a
new
row on Row7 on Sheet2 has to be inserted. So I Copy Row, then Insert
Copied
Row and start to replace some of the new figures in that row including
B7.
When I go back to the Sheet1 - the formula above has changed to:
=Sheet1!$B$8

Basically I want the formula on Sheet1 ALWAYS to point to B7 on Sheet2 -
how
do I do that?

Nick