Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |