Macro for changing Formulas to Values

Bright01
Bright01 used Ask the Experts™
on
EE Proessionals,

I  have a rather complex Worksheet that I'm putting together to calculate Life-TIme-Value.  I need a macro that I've started to write but it doesn't work.  The Macro is in Module 1, called Sub Formula_to_Absolute_Click(). What it is suppose to do is take any value (created by the formulas) and change it from formulas to "absolute values" and by pressing the button again, it needs to return to the original formulas.  The Range should be E5:Z9.

That's it!   Much thanks in advance.

B.
Macro-for-Formulas-to-Absolute-a.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JPIT Director

Commented:
In order to do this you would have to store the original formula in another cell. Otherwise when someone saved the file you would be left with whatever data was last in the cells. So if you converted to the absolute value and the file was saved you would have no way to go back to the formulas.
JPIT Director

Commented:
The other question is why? Why do you need the absolute value and not the result of the formula?

Author

Commented:
Peetjh,

Thank you for the reply.  The reason is that I'm trying to create two scenarios.  Scenario 1 says that we assume that all of the values are = across the elected number of years (formulas).  Scenario 2 says, there are differences in the Rev., change in COGS or Fixed costs in different years and you have to enter different amounts (values).  That's as simple as it gets.  If there is a different way to do  that, "enter values, where there are formulas, then that may be a different approach to the same situation.

Any ideas?

B.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

JPIT Director

Commented:
I think this may be close to what your looking for. All I did is change the checking of the btn.caption the way it was setup it would never be customized. I was thinking your formulas may be much more complex than something that could be easily copied to a range. Oh I also changed the formula so it would stay consistent across the columns is was not set for absolutes.
Macro-for-Formulas-to-Absolute-a.xlsm

Author

Commented:
peetjh,

Maybe I wasn't very clear.  If you go to a cell that has been populated by a copied formula and you want to put in a change; Let's take F5.  It's $500.  If I want to make that $600, it won't let me.  The idea here is that if one condition ("consistent") is selected, I can't change it; however, if I select "Values", I can substitute a number for a formula and it will behave properly.  The other challenge is that when I reset the model, I want to put the copy formula condition back on.

Make sense?

B.
JPIT Director

Commented:
You have another macro that runs when a cell is changed it is overwriting the changes you are making to F5

Author

Commented:
peetjh,

Yes... I think you are right.  I do not know how to stop it from reacting automatically.  The idea was that when you hit the button to change from consistent (with formulas copied for "consistency") to "values" (with formulas disabled -- so you could put in values); that's what I thought would solve the problem and create the appropriate situation.

Make sense?

B.
JPIT Director

Commented:
Here, I have commented out the macro that runs when values are changed but not sure if you need it still.
Macro-for-Formulas-to-Absolute-a.xlsm

Author

Commented:
peetjh,

This doesn't work.  Let's try this in a different way..... I have attached a newer file that actually allows for a reset.  What I am trying to do is have BUTTON 2 create a condition where I can input VALUES (instead of formulas) in the Rows 5, 6 and 8 beginning in Column E and going as far as the dates go out (as selected in Cell D3).  When BUTTON 2 is hit again, the formulas are put back in and the values are replaced.  A reset does exactly what has now been built in.

See if that makes sense to you.

B.
Client-LTV-Assessment-v2.xlsm
JPIT Director

Commented:
k, I think this will work, you only need to click the button to change back to formulas. You can type in any values over the formula you want.
Client-LTV-Assessment-v2.xlsm

Author

Commented:
peetjh,

Thanks for the note.  When I click the button, nothing happens.... and the formula doesn't come back into play. So if I go to G5 and change the formula =G4 to $700, it changes the cell to $700. But it doesn't go back to the formula when I click the button.

Once I have this working right, I have a related question to the formulas.  If you look at the formula in a column, you will see that the formulas in 7 and 9 are different then the others.... instead of a formula of = previous column, it's actually E5+E6=E7 and E7+E8=E9..... I'm looking for those formulas to remain in place and not be changed.  

Can you take a look at the Fx macro you created?

Thank you,

b.
IT Director
Commented:
Ok I fixed the Fx macro. I think I have the other calculation working the way you want also.
Client-LTV-Assessment-v2.xlsm

Author

Commented:
Peetjh,

Great job!  Just integrated it into the larger spreadsheet and it worked great.  I'll be adding a follow on question shortly since I'm going to get stuck on the next phase of this model.  Hope you will pick it up.  Thanks again.  Appreciate your efforts.

B.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial