| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| text in a cell invoke constant date in another cell - how | Excel Discussion (Misc queries) | |||
| Is there a way to set a cell value to be constant in formulas? | Excel Worksheet Functions | |||
| How do I add a formula to a cell containing a constant value | Excel Discussion (Misc queries) | |||
| Constant cell | Excel Worksheet Functions | |||
| the cell currently being evaluated contains a constant | Excel Worksheet Functions | |||
Linear Mode
