Overwrite a cell that is calculated?

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,329

Office Version

1. 365

Platform

1. Windows

• Sep 23, 2005

• #2

If I understand you correctly, there *MAY* be an acceptable workaround.

Let's say that the calculation is in cell C1. And let's say it is something really basic, like =A1+B1. Now if they want the abaility to override it, you could use cell D1, and say if there is a value in D1, use that, else use the calculation.

So essentially, your formula in C1 should change to:
=IF(LEN(D1)>0,D1,A1+B1)

That way you have the ability to override C1 without losing the calculation formula.

 

Upvote 0

B

bandit_1981

Board Regular

Joined

Aug 17, 2005

Messages

201

• Sep 23, 2005

• #3

i like it!

 

Upvote 0

B

bandit_1981

Board Regular

Joined

Aug 17, 2005

Messages

201

• Oct 3, 2005

• #4

Ok i liked it but the bosses did not. It adds to many columns to the Spread Sheet. I dont think it can be done any other way though unless i leave excel calcs and use vba. Any Ideas.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,329

Office Version

1. 365

Platform

1. Windows

• Oct 3, 2005

• #5

A cell cannot contain a formula and a hard-coded value simulaneously. It has to be one or the other. If they overwrite the formula with a hard-coded value, then the formula will be gone.

You can write VBA that can automatically populate a cell with a formula, i.e. if there a hard-coded value in the cell, and they delete it, you can have VBA automatically put the formula back in the cell.

If you have an some other idea of how you this should work, let us know and we can let you know if it is possible.

 

Upvote 0

B

bandit_1981

Board Regular

Joined

Aug 17, 2005

Messages

201

• Oct 3, 2005

• #6

That is what i was thinking. That way once they delete the overriden the formula would come back and work again. I think that just might work. Would i just accomplish this. A loop in the selection change checking for a forumla and using the target.address for a ref?

Thanks

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,329

Office Version

1. 365

Platform

1. Windows

• Oct 3, 2005

• #7

I was thinking more of using the Worksheet_Change event.

You can use the Intersect method to see if your changed cell is in the range where the formula should occur (see Intersect in Excel VBA help for a good example).

Also, you want to check for the Target cell to be empty or blank (Target=""), as that will denote that the value has been deleted.

Here is a great write-up on event procedures:
http://www.cpearson.com/excel/events.htm

If you need help please post back. I will be in and out of meetings most of the day.

 

Upvote 0

B

bandit_1981

Board Regular

Joined

Aug 17, 2005

Messages

201

• Oct 3, 2005

• #8

I might be using the intersect wrong but once it goes in to the IF the values of the formula is already lost. Would I not need to grab the value before the cell is changed. SOrry to be such a pain I am new to excel.

 

Upvote 0

B

bandit_1981

Board Regular

Joined

Aug 17, 2005

Messages

201

• Oct 3, 2005

• #9

Ok i have wirtten the code to store out the formula and the nput it back now i just need a way to store it out and i will be gold.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,329

Office Version

1. 365

Platform

1. Windows

• Oct 3, 2005

• #10

Code:

now i just need a way to store it out

What do you mean? Isn't it stored in the Worksheet_Change event?

Can you post your code, and tell me what you still need it to do that it is not already doing?

 

Upvote 0