“The Problem”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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></o>
<o> </o>
They have a column of formulas like this<o></o>
<o> </o>
=c2+2+1<o></o>
=c3+1+1<o></o>
=c4+3+1<o></o>
=c5+2+2<o></o>
…Etc…<o></o>
<o> </o>
“What the formulas means”<o></o>
The “C” column cell reference is what the price WAS in January.<o></o>
The 1<SUP>st</SUP> constant is the price increase they made April<o></o>
The 2<SUP>nd</SUP> constant is the price increase they made July<o></o>
<o> </o>
“What they want to do is…”<o></o>
Resolve all the cell references but KEEP the two constants.
The final result they want is this
=42+2+1<o></o>
=35+1+1<o></o>
=41+3+1<o></o>
=53+2+2<o></o>
<o> </o>
This is so they “can see what each quarters increase was as well as the starting price in January”
<o> </o>
(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> </o>
And what about next quarter?
<o> </o>
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></o>
=35+1+1+1<o></o>
=41+3+1+3<o></o>
=53+2+2+2<o></o>
<o> </o>
No kidding!! That is what they want to do!!!
<o> </o>
<o> </o>
“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> </o>
Thanks all in advance,
<o> </o>
<o>Regards</o>
<o>Matthew</o>
<o> </o>
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></o>
<o> </o>
They have a column of formulas like this<o></o>
<o> </o>
=c2+2+1<o></o>
=c3+1+1<o></o>
=c4+3+1<o></o>
=c5+2+2<o></o>
…Etc…<o></o>
<o> </o>
“What the formulas means”<o></o>
The “C” column cell reference is what the price WAS in January.<o></o>
The 1<SUP>st</SUP> constant is the price increase they made April<o></o>
The 2<SUP>nd</SUP> constant is the price increase they made July<o></o>
<o> </o>
“What they want to do is…”<o></o>
Resolve all the cell references but KEEP the two constants.
The final result they want is this
=42+2+1<o></o>
=35+1+1<o></o>
=41+3+1<o></o>
=53+2+2<o></o>
<o> </o>
This is so they “can see what each quarters increase was as well as the starting price in January”
<o> </o>
(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> </o>
And what about next quarter?
<o> </o>
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></o>
=35+1+1+1<o></o>
=41+3+1+3<o></o>
=53+2+2+2<o></o>
<o> </o>
No kidding!! That is what they want to do!!!
<o> </o>
<o> </o>
“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> </o>
Thanks all in advance,
<o> </o>
<o>Regards</o>
<o>Matthew</o>
<o> </o>