Thanks for your time.

When I put this in .. =IF(INDIRECT('Jon Wickett'!C20)="JAN","",'Jon
Wickett'!C20)

I get #Ref! in the first sheet. Then when I drag and drop the cell in
sheet to both ther 'c20' change to #Ref!.

Is it me?

Tried the other too, and cant get it to working either. It must be me!

Barb

"Ron Coderre" wrote:

Well, the first formula problem is my fault for trusting the formula you
posted without checking it for errors.

It should be this:
=IF(INDIRECT("Sheet2!A5")="JAN","",Sheet2!A5)

...The sheet/cell separator is an exclamation mark (!), not a colon (:)

It's not possible for the left part of that formula to reference anything
other than Sheet2!A5

And this one: =INDEX(Sheet2!$1:$65536,5,1)
behaves properly, too. It always refers to Sheet2!A5, even if I move it,
delete it, paste over it, insert/delete rows, or insert/delete columns.

Note: It will, however, break if you delete ALL rows or ALL columns

What are you doing that breaks the formulas?

***********
Regards,
Ron

XL2002, WinXP


"Beads" wrote:


Tried both and neither work.

On a control page[sheet 1] I want the formula to always read 'A5' in sheet
2. While in sheet 2 if I move the word Jan in A5 by drag and drop, I do not
want the control sheet to move the cell reference 'A5' to move with it. As
mentioned I have tried the $ lock, but it still moves.

Beads

"Beads" wrote:

I am trying to lock a cell reference in one sheet, so when data in another
page is moved the reference remains. I have tried the $ lock, but it still
moves.

formula :

=If(Sheet2:A5="JAN","",Sheet2:A5)

it is the first A5 which I do not want to be moved not matter what happens
to the cell.

This information will be held in Sheet one. So if I click and drag
information in Sheet 2 to a different cell, I want the formula to remain
readinf A5

Help please