Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Macro for changing Formulas to Values

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
Avatar of JP
JP
Flag of United States of America image

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.
The other question is why? Why do you need the absolute value and not the result of the formula?
Avatar of Bright01

ASKER

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.
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
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.
You have another macro that runs when a cell is changed it is overwriting the changes you are making to F5
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.
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
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
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
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.
ASKER CERTIFIED SOLUTION
Avatar of JP
JP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.