Reference Formula locked on a cell

[hamburg18w]

I am running MS Exel 2003. Sheet1 has a list of roling names and dates, meaning the names on top get deleted and new names get added on the bottom. Sheet2, 3, 4, etc. point to specific cells in a specific row on sheet1. I am looking for a formula that would stay locked on a specific cell in sheet1. Example: In sheet2, row 4 if have a list of formulas that point to sheet1, row 4. When I delete the name in sheet1 row 5, the formulas in sheet2 row 4 no longer point to sheet1, row 4. Is there a way to make the formula that points to sheet1 row 4 remain constant even when I delete the row and new information is now in row 4?

Thanks for your help.
Juan

 

[xlbo]

use the INDIRECT formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

use the INDIRECT function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hamburg18w]

I am getting the #REF! error. Following is the formula:

=INDIRECT(Sheet1!$D$4)

Do I need to make changes to the formula?

Thanks,
Juan

 

[SkipVought]

put Sheet1!$D$4 in a cell and reference that cell in the INDIRECT

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

or put quotes around it...
[tt]
=INDIRECT("Sheet1!$D$4")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hamburg18w]

Thanks. You have been great? I do have another question. I have one column that includes a SUM formula. Can you combine the INDIRECT and the SUM formulas? I have tried it but did not give me the correct results. Below is the formula that I tried:

=INDIRECT(SUM("Sheet1!$E$4","Sheet1!$G$4","Sheet1!$I$4","Sheet1!$K$4"))
or
=INDIRECT(SUM(Sheet1!$E$4,Sheet1!$G$4,Sheet1!$I$4,Sheet1!$K$4))

Neither of these produced the appropriate results. Thanks for your help.

Juan

 

[SkipVought]

[tt]
=SUM(INDIRECT("Sheet1!$E$4"),INDIRECT("Sheet1!$G$4"),INDIRECT("Sheet1!$I$4"),INDIRECT("Sheet1!$K$4"))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]