Preventing automatic adjustment of formual reference

A

Andrew

I have a formula on Sheet2 that refers to a cell on Sheet1. As a simple
example, cell A12 on Sheet2 contains the formula:
=Sheet1!A12

I want this formula to always refer to cell A12 on Sheet1, irrespective of
any changes that are made to Sheet1. At the moment, if a row is inserted
above row 12 on Sheet1, then the formula is automatically adjusted to:
=Sheet1!A13

I do not want this behavoiur; I want it to continue refering to cell A12.
Is this possible? Using an absolute reference ($A$12) makes no difference.
 
A

Andrew

Frank said:
Hi
=INDIRECT("'sheet1'!A12")

Thanks. That partially answers my question. At least I know it is
possible. However, I over simplified my problem. What I actually want is
Sheet2 to be a copy of an area of Sheet1. For example, I would like the
cells on Sheet2 A1:C200 to be a copy of the contents of Sheet1 B11:D210.

So, in Cell A1 I would enter the formula: =indirect("'sheet1'!B11"). I
could manually enter the formula into all 300 cells, but this would not be
very practical. What I need is a formula that I can copy on Sheet2, as
would be possible with the formula =B11.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top