nyac13
03-21-2012, 07:45 PM
Hi all,
I am trying to take an excel formula I created that contains multiple functions and replacing one of the functions with its calculated value while retaining all other formulas and links within the cell. It would help me tremendously if I could get a macro to do this for an entire data table within a worksheet.
For example, my formula in each cell of the table looks something like this:
=ROUND(SUM($D$3,$E$3,$F$3)/100,0)+D10
In cells D3, E3, and F3, I have the values 100, 200, and 300 respectively. Basically, I want the macro to automatically turn the formula above into:
=ROUND(600/100,0)+D10
...with the SUM formula being replaced with its calculated value, and the ROUND function and the link to cell D10 being retained.
This would be the same process as going into the formula of each cell, highlighting the SUM() formula, and pressing F9 to change it to its calculated value. My problem lies with the fact that I need this done in many different cells within a data table.
If somebody could help me, I would very much appreciate it!
Thanks,
Nick
I am trying to take an excel formula I created that contains multiple functions and replacing one of the functions with its calculated value while retaining all other formulas and links within the cell. It would help me tremendously if I could get a macro to do this for an entire data table within a worksheet.
For example, my formula in each cell of the table looks something like this:
=ROUND(SUM($D$3,$E$3,$F$3)/100,0)+D10
In cells D3, E3, and F3, I have the values 100, 200, and 300 respectively. Basically, I want the macro to automatically turn the formula above into:
=ROUND(600/100,0)+D10
...with the SUM formula being replaced with its calculated value, and the ROUND function and the link to cell D10 being retained.
This would be the same process as going into the formula of each cell, highlighting the SUM() formula, and pressing F9 to change it to its calculated value. My problem lies with the fact that I need this done in many different cells within a data table.
If somebody could help me, I would very much appreciate it!
Thanks,
Nick