|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 02:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com