Hi

Ron put double quotes around the expression inside the parentheses.
Try
=IF(INDIRECT("'Jon Wickett'!C20")="JAN","",'Jon Wickett'!C20)


--
Regards

Roger Govier


"Beads" wrote in message
...
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