Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to overwrite a value without loosing the formula?
A weekly workbook has 7 sheets, one for each day.
My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#2
|
|||
|
|||
What's the manual value you need to enter, and what's in A1 when you enter
it? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Mark Dvorkin" wrote in message ... A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#3
|
|||
|
|||
a1 of sheet1 (Monday) holds an int entered manually, which often but
not always goes into a2 of the sheet2 (Tuesday) and so on. Sometimes I need to overwrite this value in a2 on the subsequent sheet and I would like to do it without loosing the formula in a2 which most likely will be used in sheet3, 4 and so on. Anne Troy wrote: What's the manual value you need to enter, and what's in A1 when you enter it? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Mark Dvorkin" wrote in message ... A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#4
|
|||
|
|||
Well, I just can't help but think that I'd be trying to figure a different
cell to use. Something like this: =if(isblank(G1),A1,G1). This way, you can enter something in G1 whenever you want. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Mark Dvorkin" wrote in message ... a1 of sheet1 (Monday) holds an int entered manually, which often but not always goes into a2 of the sheet2 (Tuesday) and so on. Sometimes I need to overwrite this value in a2 on the subsequent sheet and I would like to do it without loosing the formula in a2 which most likely will be used in sheet3, 4 and so on. Anne Troy wrote: What's the manual value you need to enter, and what's in A1 when you enter it? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Mark Dvorkin" wrote in message ... A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#5
|
|||
|
|||
Hi!
Unfortunately, this cannot be done. Maybe use a helper cell when you need to manually enter some other value. Then in the current formula, test the helper cell for being blank. If the helper cell is blank then the formula stays as is. If the helper cell is not blank then use the helper cell in the formula. Biff "Mark Dvorkin" wrote in message ... A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#6
|
|||
|
|||
Sure, as long as you don't mind using a bit of VBA.
Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A2" Then If IsEmpty(.Value) Then Application.EnableEvents = False .Formula = "=prevDay(A1)" Application.EnableEvents = True End If End If End With End Sub When you overwrite the formula, the value you input will stick. When you delete that value, the code above will put the formula back into the cell. In article , Mark Dvorkin wrote: A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#7
|
|||
|
|||
Hmmm...
Very nifty! I see the formula can't be accidentally deleted either. Biff "JE McGimpsey" wrote in message ... Sure, as long as you don't mind using a bit of VBA. Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A2" Then If IsEmpty(.Value) Then Application.EnableEvents = False .Formula = "=prevDay(A1)" Application.EnableEvents = True End If End If End With End Sub When you overwrite the formula, the value you input will stick. When you delete that value, the code above will put the formula back into the cell. In article , Mark Dvorkin wrote: A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
#8
|
|||
|
|||
Hope nobody minds me jumping in on this thread with a further question - but perhaps this can be modified to help with something I am looking for? At the moment I have some forumula spreading a budget based on various user selectable options (eg previous yr, flat, etc) in another cell drop down. one of the options is to manually input At the moment I have used conditional formatting to turn the cell white while while waiting for input and then pink once the formula has been overwritten. This works well to highlight overwritten cells to make the user aware that forumla is gone - but I would really like the formula to return if one of the other options is selected. I am very new to VBA but wondered if the code could help with the above? If it is possible - could you annotate the code to explain what it is doing? Ruthki -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=385894 |
#9
|
|||
|
|||
Seems to me it might be a better choice to use a different sheet for manual
input -vs- formulas. Then, you could put the "Input Type" into a dropdown. For "Manual" you can have the formulas look at a worksheet called "Manual", and for formulas it uses the current worksheet or "Formula" worksheet. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Ruthki" wrote in message ... Hope nobody minds me jumping in on this thread with a further question - but perhaps this can be modified to help with something I am looking for? At the moment I have some forumula spreading a budget based on various user selectable options (eg previous yr, flat, etc) in another cell drop down. one of the options is to manually input At the moment I have used conditional formatting to turn the cell white while while waiting for input and then pink once the formula has been overwritten. This works well to highlight overwritten cells to make the user aware that forumla is gone - but I would really like the formula to return if one of the other options is selected. I am very new to VBA but wondered if the code could help with the above? If it is possible - could you annotate the code to explain what it is doing? Ruthki -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=385894 |
#10
|
|||
|
|||
By the way, if you put that dropdown into (for instance) A1, you could use
=INDIRECT to get the Sheetname so your cell references switch sheets. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Anne Troy" wrote in message news:c08d1$42d0ca96$97c5108d$4874@allthenewsgroups .com... Seems to me it might be a better choice to use a different sheet for manual input -vs- formulas. Then, you could put the "Input Type" into a dropdown. For "Manual" you can have the formulas look at a worksheet called "Manual", and for formulas it uses the current worksheet or "Formula" worksheet. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Ruthki" wrote in message ... Hope nobody minds me jumping in on this thread with a further question - but perhaps this can be modified to help with something I am looking for? At the moment I have some forumula spreading a budget based on various user selectable options (eg previous yr, flat, etc) in another cell drop down. one of the options is to manually input At the moment I have used conditional formatting to turn the cell white while while waiting for input and then pink once the formula has been overwritten. This works well to highlight overwritten cells to make the user aware that forumla is gone - but I would really like the formula to return if one of the other options is selected. I am very new to VBA but wondered if the code could help with the above? If it is possible - could you annotate the code to explain what it is doing? Ruthki -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=385894 |
#11
|
|||
|
|||
boy, this is cute!
when I posted my question I was convinced there is no way it could be done, but this is cool ... JE McGimpsey wrote: Sure, as long as you don't mind using a bit of VBA. Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A2" Then If IsEmpty(.Value) Then Application.EnableEvents = False .Formula = "=prevDay(A1)" Application.EnableEvents = True End If End If End With End Sub When you overwrite the formula, the value you input will stick. When you delete that value, the code above will put the formula back into the cell. In article , Mark Dvorkin wrote: A weekly workbook has 7 sheets, one for each day. My function say in cell a2 (=prevDay(a1)) puts the value from cell a1 of the previous day into cell a2. Sometimes I need to overwrite this value manually. Is there a way of doing this without loosing the formula in a2? thanks in advance, /mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm loosing colour when copying charts from excel to word. | Charts and Charting in Excel | |||
Excel prompts me to save a copy or overwrite file. | Excel Worksheet Functions | |||
Help - Loosing my settings and my mind. | Excel Discussion (Misc queries) | |||
Add data to cell w/o loosing initial data | Excel Discussion (Misc queries) | |||
Overwrite changes dialogue box | Excel Discussion (Misc queries) |