Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Lock cell reference in formula, even when moved with click & drag

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Lock cell reference in formula, even when moved with click & drag

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


"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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Lock cell reference in formula, even when moved with click & drag


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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Lock cell reference in formula, even when moved with click & d

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Lock cell reference in formula, even when moved with click & d

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Lock cell reference in formula, even when moved with click & d

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Lock cell reference in formula, even when moved with click & d

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


"Roger Govier" wrote:

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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Lock cell reference in formula, even when moved with click & d

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

"Ron Coderre" wrote:

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


"Roger Govier" wrote:

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




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Lock cell reference in formula, even when moved with click & d

Regarding:
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.<<

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


"Beads" wrote:

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

"Ron Coderre" wrote:

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


"Roger Govier" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
Protect Workbook vs Worksheet?? Dan B Excel Worksheet Functions 3 November 7th 05 10:02 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 01:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"