how to overwrite a value without loosing the formula?

  • Thread starter Thread starter Mark Dvorkin
  • Start date Start date
M

Mark Dvorkin

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


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



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
 
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
 
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.
 
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
 
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
 
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 said:
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
Click to expand...
 
boy, this is cute!
when I posted my question I was convinced there is no way it could be done,
but this is cool ...
 
Back
Top