Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
HW
 
Posts: n/a
Default 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
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



  #3   Report Post  
HW
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"