| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I lock a linked cell formula??
I am creating a template for a workbook of forms. Each sheet links to the previous one. (ie once they enter data to the first sheet it copies across to all sheets so they don't have to re-enter the data every time). I have done this by linking the cells. However I want to protect the link formula so that if they enter data then delete it they only delete the data not the link formula. Is this possible...please help! -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
You only need to protect the other sheets, because that's where the formula is.
"lauraroebuck" wrote: I am creating a template for a workbook of forms. Each sheet links to the previous one. (ie once they enter data to the first sheet it copies across to all sheets so they don't have to re-enter the data every time). I have done this by linking the cells. However I want to protect the link formula so that if they enter data then delete it they only delete the data not the link formula. Is this possible...please help! -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
But they need to enter data on the other sheets. What happens is they can overwrite the data that has copied across if it has changed. It has conditional formatting so that any changes are hightlighted as red So thanks for your reply but this does not help me  -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
So just lock the cells you need. and leave the other editable
"lauraroebuck" wrote: But they need to enter data on the other sheets. What happens is they can overwrite the data that has copied across if it has changed. It has conditional formatting so that any changes are hightlighted as red So thanks for your reply but this does not help me -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
I can't as it is the same cells that they need to enter data into -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
You can't have it both ways. If you want it to be formula then they're not
suuposed to fill in information into those cells. They are supposed to be calculated with every change in the varinats in the formula "lauraroebuck" wrote: I can't as it is the same cells that they need to enter data into -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Thanks but you obviously don't understand my requirements and your responses are not really helping me -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
So perhaps you might consider the situation where the only person who has
tried to help you "obviously doesn't understand your requirements", and then you might consider whether you have unambiguously specified your requirements. Which cells have formulae? Which cells are you wanting to leave free for the user to insert data? Perhaps you could give an example? If you have a cell with a formula and in that cell you are allowing a user to overwrite the formula with data, then you are not subsequently going to be able to retrieve the formula in that cell because it does not exist there after you have over-written it with data; that is what over-writing does. -- David Biddulph "lauraroebuck" wrote in message ... Thanks but you obviously don't understand my requirements and your responses are not really helping me -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Apologies to assaf1978 if my reply seemed abrupt! I realise my explanation is not that clear - hard to explain via text rather than face to face! This is the first time I have used a forum like this some replies feel patronising. Yes David you are right in your explanation "..have a cell with a formula and in that cell you are allowing a user to overwrite the formula with data, then you are not subsequently going to be able to retrieve the formula in that cell because it does not exist there after you have over-written it with data; that is what over-writing does" I am aware of this and this is the point of my question is there anyway to lock the formula only do that it does not get deleted? thanks -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
lauraroebuck;334998 Wrote: I am creating a template for a workbook of forms. Each sheet links to the previous one. (ie once they enter data to the first sheet it copies across to all sheets so they don't have to re-enter the data every time). I have done this by linking the cells. However I want to protect the link formula so that if they enter data then delete it they only delete the data not the link formula. Is this possible...please help! Welcome to the board!, perhaps, do this on your other sheets: press Ctrl+A (all cells selected), right click in selection, choose format cells, Protection tab uncheck locked click ok, select A1 repeat the same but check locked and hidden then protect the worksheet, now the whole sheet will be editable but the formula protected! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Yes, if you lock the cell with the formula in it, this will prevent anyone
from typing data over the top of the formula. -- David Biddulph lauraroebuck wrote: Apologies to assaf1978 if my reply seemed abrupt! I realise my explanation is not that clear - hard to explain via text rather than face to face! This is the first time I have used a forum like this some replies feel patronising. Yes David you are right in your explanation "..have a cell with a formula and in that cell you are allowing a user to overwrite the formula with data, then you are not subsequently going to be able to retrieve the formula in that cell because it does not exist there after you have over-written it with data; that is what over-writing does" I am aware of this and this is the point of my question is there anyway to lock the formula only do that it does not get deleted? thanks |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
lauraroebuck;335460 Wrote: Apologies to assaf1978 if my reply seemed abrupt! I realise my explanation is not that clear - hard to explain via text rather than face to face! This is the first time I have used a forum like this some replies feel patronising. Yes David you are right in your explanation "..have a cell with a formula and in that cell you are allowing a user to overwrite the formula with data, then you are not subsequently going to be able to retrieve the formula in that cell because it does not exist there after you have over-written it with data; that is what over-writing does" I am aware of this and this is the point of my question is there anyway to lock the formula only do that it does not get deleted? thanks Did you see this post http://www.thecodecage.com/forumz/335465-post10.html -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Thanks Simon but his doesn't work either as if they overtype then delete it still deletes the formula. I want the formula to be protected from being deleted. Starting to think it is not possible! -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
lauraroebuck;338204 Wrote: Thanks Simon but his doesn't work either as if they overtype then delete it still deletes the formula. I want the formula to be protected from being deleted. Starting to think it is not possible!Laura it's not possible!, here check out the test book i have done for you! +-------------------------------------------------------------------+ |Filename: Laura testbook.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=130| +-------------------------------------------------------------------+ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Thanks Simon, sorry I am not very good at explaining what I need - they still need to be able to type in the cell! -- lauraroebuck ------------------------------------------------------------------------ lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93655 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I lock a linked cell formula??
Yes. You are now beginning to understand what we've been saying. When
someone overtypes the formula with data, the formula isn't there in the cell any more. It has gone. It is no longer a formula. You no longer have a formula. You have data instead of the formula. The cell can't contain both data and formula at the same time. It can contain only one or the other, not both. -- David Biddulph lauraroebuck wrote: Thanks Simon but his doesn't work either as if they overtype then delete it still deletes the formula. I want the formula to be protected from being deleted. Starting to think it is not possible! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FORMULA/FUNCTION TO LOCK AND UNLOCK A CELL | Excel Discussion (Misc queries) | |||
| formula to lock cell? | Excel Discussion (Misc queries) | |||
| I want to copy a formula down several rows and lock a cell ref. | Excel Discussion (Misc queries) | |||
| In creating a spreadsheet how can I lock a formula cell only? | New Users to Excel | |||
| How do I lock a formula in a cell in an Excel worksheet? | Excel Worksheet Functions | |||
Linear Mode
