Lock cell reference in formula, even when moved with click & drag

[Guest]

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

 

[Guest]

Offhand, I can think of 2 options....

This one is volatile
=IF(INDIRECT("Sheet2:A5")="JAN","",Sheet2:A5)

This one is not
=IF(INDEX(Sheet2!$1:$65536,5,1)="JAN","",Sheet2:A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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

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

 

[Roger Govier]

Hi

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


--
Regards

Roger Govier

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

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

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

:

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

 

[Guest]

Another example of why it's so important to post the actual formula (whenever
possible), so we know exactly what we're dealing with.

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

XL2002, WinXP

Hi

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


--
Regards

Roger Govier

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

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


:


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

:

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

 

[Guest]

The formula is exactly as you sent me, so I o not understand the double
paranthasis issue, It has them,

Sorry if I do not understand, but this is why I was hopping for help. I hae
not used these boards before, and did not appreciate I should include the
formula. I know now though.

Regards

Barbara

Another example of why it's so important to post the actual formula (whenever
possible), so we know exactly what we're dealing with.

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

XL2002, WinXP

Hi

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


--
Regards

Roger Govier

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

:

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


:


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

:

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

 

[Guest]

Regarding:not used these boards before, and did not appreciate I should include the
formula. I know now though.<<

Don't worry about it, Barbara....It takes a little while to learn what
information needs to be included when posting a question.

***************************
Regarding the formula.....here's a little clarification:
When a sheet tab name has a space in it, the name must be enclosed within
single quotes:

='sheet with a space'!A1

The INDIRECT function converts text that looks like a reference into and
actual reference. When you type the text directly in that function, it must
be enclosed within doublt-quotes:

=INDIRECT("'sheetname with spaces'!A1")

Here's a better view of that formula:
=INDIRECT(" ' sheetname with spaces ' !A1 ")

Note: The extra spaces make in non-functional, but easier to see the double
and single quotes

Looking forward to seeing many more posts by you!
***********

Regards,
Ron

XL2002, WinXP

The formula is exactly as you sent me, so I o not understand the double
paranthasis issue, It has them,

Sorry if I do not understand, but this is why I was hopping for help. I hae
not used these boards before, and did not appreciate I should include the
formula. I know now though.

Regards

Barbara

Another example of why it's so important to post the actual formula (whenever
possible), so we know exactly what we're dealing with.

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

XL2002, WinXP

Hi

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


--
Regards

Roger Govier


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

:

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


:


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

:

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