how to overwrite a value without loosing the formula?

Mark Dvorkin

unread,

Jul 9, 2005, 9:11:05 PM7/9/05

to

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

Anne Troy

unread,

Jul 9, 2005, 9:17:34 PM7/9/05

to

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" <mdvo...@rcn.com> wrote in message
news:42D075A9...@rcn.com...

Mark Dvorkin

unread,

Jul 9, 2005, 9:48:54 PM7/9/05

to

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

unread,

Jul 9, 2005, 10:18:07 PM7/9/05

to

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" <mdvo...@rcn.com> wrote in message

news:42D07E86...@rcn.com...

Biff

unread,

Jul 9, 2005, 10:32:51 PM7/9/05

to

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" <mdvo...@rcn.com> wrote in message
news:42D075A9...@rcn.com...

JE McGimpsey

unread,

Jul 9, 2005, 11:11:47 PM7/9/05

to

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 <42D075A9...@rcn.com>, Mark Dvorkin <mdvo...@rcn.com>
wrote:

Biff

unread,

Jul 10, 2005, 12:19:56 AM7/10/05

to

Hmmm...

Very nifty!

I see the formula can't be accidentally deleted either.

Biff

"JE McGimpsey" <jemcg...@mvps.org> wrote in message
news:jemcgimpsey-8913...@msnews.microsoft.com...

Ruthki

unread,

Jul 10, 2005, 2:59:53 AM7/10/05

to

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?action=getinfo&userid=24503
View this thread: http://www.excelforum.com/showthread.php?threadid=385894

Anne Troy

unread,

Jul 10, 2005, 3:13:30 AM7/10/05

to

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" <Ruthki.1rxzec_...@excelforum-nospam.com> wrote in
message news:Ruthki.1rxzec_...@excelforum-nospam.com...

Anne Troy

unread,

Jul 10, 2005, 3:16:10 AM7/10/05

to

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" <n...@officearticles.com> wrote in message
news:c08d1$42d0ca96$97c5108d$48...@allthenewsgroups.com...

Mark Dvorkin

unread,

Jul 10, 2005, 9:12:31 PM7/10/05

to

boy, this is cute!
when I posted my question I was convinced there is no way it could be done,
but this is cool ...