Cell referencing between differnt worksheets

[John]

There are 2 worksheets. Say I want cell A2 in sheet2 ALWAYS to equal cell A2
in sheet1. The data in sheet1 is cut and pasted quite a lot due to the way
we work and when this happens the link is corrupted. I think I could use
INDIRECT but there are quite a lot of links and if I used this method what is
the best way to set it up - does every cell have to be set up individually?
A lot of work!!!

 

[David Biddulph]

In cell A2 on sheet 2, type the = sign, then click on sheet 1, click in cell
A2, & the formula should show =Sheet1!A2
Accept the formula.

 

[Stephen]

There are 2 worksheets. Say I want cell A2 in sheet2 ALWAYS to equal cell
A2
in sheet1. The data in sheet1 is cut and pasted quite a lot due to the
way
we work and when this happens the link is corrupted. I think I could use
INDIRECT but there are quite a lot of links and if I used this method what
is
the best way to set it up - does every cell have to be set up
individually?
A lot of work!!!

If I understand you correctly, you want a formula that will always reference
Sheet1!A2, even if another cell is cut and pasted at that location (thus
becoming Sheet1!A2). If that is so, you could use
=INDIRECT("Sheet1!A2")

However, as I think you may already have discovered, if you then copy this
to another cell in Sheet2, the reference does not change - you get an exact
copy of the original. One way you could use to get around this is to make up
the text string argument for the INDIRECT formula by concatenating a shorter
text string for the sheet name and a cell address that will change depending
on where the formula is located:
=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))

Does this solve your problem?

 

[John]

This looks like the magic I have been searching for. I'll give it a go after
xmas and let you know. Thanks very much for your help. Merry xmas.