do cell refernces have to move when I cut and paste?
 

I'm trying to something verrrry simple, and being outsmarted by
Excel...

What I want is for sheetX to ALWAYS read from sheetY's first cells A2,
A3, A4, etc, no matter how I rearrange, delete, insert rows in sheetY.

In more detail - ( note that this is a simple ( contrived ) example to
illustrate the problem ):

I have sheet1:

*price*
$22.00
$44.00
$88.00

and sheet2:

*half price*
$11.00
$22.00
$44.00

sheet2 cells contain this formula:
=sheet1!$A$2/2
=sheet1!$A$3/2
=sheet1!$A$4/2

Now the problem: If I delete a row in sheet1, I get a #REF error. Or
if I add a new row in the middle of sheet1, it is not reflected in
sheet2.

What I want is for sheet2 to ALWAYS read from the values of sheet1's
first 3 cells, no matter how I rearrange them.

Is this possible, or Excel just toooooo smart for this?

In case this is unlcear, I've posted a similar example he
http://thegoldensun.com/misc/test.xls

Thanks *very* much for any help : )

Harlan

Use INDIRECT, i.e.

=INDIRECT("Y!A2")

will always refer to A2 regardless if you add or delete rows/columns
using your example

=INDIRECT("Sheet1!A2")/2

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"HW" wrote in message
om...
I'm trying to something verrrry simple, and being outsmarted by
Excel...

What I want is for sheetX to ALWAYS read from sheetY's first cells A2,
A3, A4, etc, no matter how I rearrange, delete, insert rows in sheetY.

In more detail - ( note that this is a simple ( contrived ) example to
illustrate the problem ):

I have sheet1:

*price*
$22.00
$44.00
$88.00

and sheet2:

*half price*
$11.00
$22.00
$44.00

sheet2 cells contain this formula:
=sheet1!$A$2/2
=sheet1!$A$3/2
=sheet1!$A$4/2

Now the problem: If I delete a row in sheet1, I get a #REF error. Or
if I add a new row in the middle of sheet1, it is not reflected in
sheet2.

What I want is for sheet2 to ALWAYS read from the values of sheet1's
first 3 cells, no matter how I rearrange them.

Is this possible, or Excel just toooooo smart for this?

In case this is unlcear, I've posted a similar example he
http://thegoldensun.com/misc/test.xls

Thanks *very* much for any help : )

Harlan

Thanks very much! I knew it was simple, but couldn't find it in the docs etc.

: )

"Peo Sjoblom" wrote in message ...
Use INDIRECT, i.e.

=INDIRECT("Y!A2")

will always refer to A2 regardless if you add or delete rows/columns