Resolve a cell reference in formula but keep constants

mateo

New Member
Joined
Aug 18, 2009
Messages
5
“The Problem”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I have a group of salesmen ( SalesHoles to me ) who want to do something that I have never been asked before. Other suggestions I give them fall on deaf (and dumb) ears and ‘are not an option’<o:p></o:p>
<o:p> </o:p>
They have a column of formulas like this<o:p></o:p>
<o:p> </o:p>
=c2+2+1<o:p></o:p>
=c3+1+1<o:p></o:p>
=c4+3+1<o:p></o:p>
=c5+2+2<o:p></o:p>
…Etc…<o:p></o:p>
<o:p> </o:p>
“What the formulas means”<o:p></o:p>
The “C” column cell reference is what the price WAS in January.<o:p></o:p>
The 1<SUP>st</SUP> constant is the price increase they made April<o:p></o:p>
The 2<SUP>nd</SUP> constant is the price increase they made July<o:p></o:p>
<o:p> </o:p>
“What they want to do is…”<o:p></o:p>
Resolve all the cell references but KEEP the two constants.
The final result they want is this
=42+2+1<o:p></o:p>
=35+1+1<o:p></o:p>
=41+3+1<o:p></o:p>
=53+2+2<o:p></o:p>
<o:p> </o:p>
This is so they “can see what each quarters increase was as well as the starting price in January”
<o:p> </o:p>
(OK here is where I really started scratching my head because they KNOW they will have to click on each cell individually to see all 3 numbers and they know you can only select 1 cell at a time, so instead of listing the increases in separate columns so you can see the Start price, 1<SUP>st</SUP> increase, 2<SUP>nd</SUP> increase and the final total all at once….. they want to click each cell individually???!!!??)
<o:p> </o:p>
And what about next quarter?
<o:p> </o:p>
They will edit all the formulas and add the 3<SUP>rd</SUP> quarter increase so it will be like this
=42+2+1+2<o:p></o:p>
=35+1+1+1<o:p></o:p>
=41+3+1+3<o:p></o:p>
=53+2+2+2<o:p></o:p>
<o:p> </o:p>
No kidding!! That is what they want to do!!! :eek:
<o:p> </o:p>
<o:p> </o:p>
“What I told them”
I told them they can edit each formula, select the cell reference and hit F9.
This will resolve the value of C2 but keep the “+2+1”
But, they need to edit each formula individually
Of course that was too much work for them and needed a way to do it for the entire product line... (about 300 rows)

If VBA is the only way to do this I would greatly appreciate the code to do this, but whenever I write code for people it always causes issues for ME… usually at 4:55PM on a Friday. Yes, I need to write better code but I prefer to stay away from it whenever possible. If not possible with a formula or other feature, I will take code.
<o:p> </o:p>
Thanks all in advance,
<o:p> </o:p>
<o:p>Regards</o:p>
<o:p>Matthew</o:p>
<o:p> </o:p>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Give this a try. I assumed the formulas are in column D *and* the code is only evaluating the first reference... not very smart code.

Code:
Option Explicit

Sub Test()
    Dim rng As Range
    Dim cll As Range
    
    Set rng = Range("D2", Cells(Rows.Count, 4).End(xlUp))
    
    For Each cll In rng
        cll.formula = EvaluateReferences(cll.formula)
    Next cll
    
End Sub

Function EvaluateReferences(ByVal formula As String) As String
    Dim v As Variant
    
    v = Split(formula, "+")
    
    v(0) = "=" & Evaluate(v(0))
    
    EvaluateReferences = Join(v, "+")
End Function
 

mateo

New Member
Joined
Aug 18, 2009
Messages
5
Juan Pablo!

Caramba! Eso fue rapido Senor!! Opps.. sorry.. this is an English only post.
Wow.. thanks for the quick reply and DEAD ON answer.

this code worked and I already replied and setup a session for tomorrow.. thanks and if you are still in Bogota keep on the lookout for a Sandra Patricia Valencia Arias! Almost married that girl while I was in the Navy... LOL...

thanks again.
mil gracias
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Juan Pablo!

Caramba! Eso fue rapido Senor!! Opps.. sorry.. this is an English only post.
Wow.. thanks for the quick reply and DEAD ON answer.

this code worked and I already replied and setup a session for tomorrow.. thanks and if you are still in Bogota keep on the lookout for a Sandra Patricia Valencia Arias! Almost married that girl while I was in the Navy... LOL...

thanks again.
mil gracias
LOL, thanks... I'm still in Bogotá, but I don't think my wife would appreciate much me being on the lookout for her.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top