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