If you can't use a macro to do this then it's gonna be a real kludge!
Luckily, I'm pretty good at kludge! <g>
Let's assume you have this formula in D1:
=A1*B1+C1
You want to add these expressions to either end:
IF(E1>5,
,A1*B1+(C1*4))
So the new formula is:
=IF(E1>5,A1*B1+C1,A1*B1+(C1*4))
Enter the expressions you want to add in 2 cells:
K1 = IF(E1>5,
L1 = ,A1*B1+(C1*4))
Create this named formula:
Insert>Name>Define
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-2]",FALSE))
OK
Are you familiar with R1C1 referencing? "RC[-2]" refers to the cell in the
current row 2 columns to the left. So, if we enter that formula in cell F1
"RC[-2]" refers to cell D1.
Enter this formula in F1:
="="&K$1&MID(Formula,2,255)&L$1
The result of that formula will be a TEXT string that looks like the formula
you want:
=IF(E1>5,A1*B1+C1,A1*B1+(C1*4))
Now, to convert that text string to a real working formula:
Copy cell F1
Then, Edit>Paste special>Values>OK
Then, Edit>Replace
Find what: =
Replace with: =
Replace all
Now you have the formula you wanted. Make sure things look ok then you can
delete your old formulas and move the new formulas to the old location.
--
Biff
Microsoft Excel MVP
"Tiziano" <(E-Mail Removed)> wrote in message
news:OAbu$(E-Mail Removed)...
> Hi, Dave.
> Thanks for the suggestion.
> I now realize that I wasn't clear enough... What I meant to say is that
> the prefix/suffix never vary in terms of formula type, however they change
> in terms of row address.
>
> So, as a simple example, the complete and new formula in D2 would be
> something like this:
> =if(E2>5,A2*B2+C2+F2,A2*B2+(C2*4))
>
> I am not sure that I will be able to run a macro due to high security
> level settings. Is there a way to solve my problem via a formula?
>
> Thanks.
> --
> tb
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> The prefix and suffix never vary???
>>
>> If that's true, you could use a macro. Select your range (all of column
>> D???)
>> and run this:
>>
>> Option Explicit
>> Sub testme()
>> Dim myCell As Range
>> Dim myRng As Range
>>
>> Set myRng = Nothing
>> On Error Resume Next
>> Set myRng = Intersect(Selection, _
>> Selection.Cells.SpecialCells(xlCellTypeFormulas))
>> On Error GoTo 0
>>
>> If myRng Is Nothing Then
>> MsgBox "No formulas"
>> Exit Sub
>> End If
>>
>> For Each myCell In myRng.Cells
>> myCell.Formula = "=if(e1>5," _
>> & Mid(myCell.Formula, 2) _
>> & ",A1*B1+(C1*4))"
>> Next myCell
>>
>> End Sub
>>
>> If you're new to macros, you may want to read David McRitchie's intro at:
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>> Tiziano wrote:
>>>
>>> I have a bunch of formulas in Column D. All these formulas are
>>> incorrectly
>>> written, so I would like to remedy that by pre-pending/appending two
>>> text
>>> strings to them; this would create new, correct formulas in the process.
>>>
>>> For the sake of simplicity, let's assume that in Cell D1 I have this
>>> original formula:
>>> =A1*B1+C1
>>>
>>> To this formula, I would like to pre-pend the string "=if(E1>5," and
>>> append
>>> the string ",A1*B1+(C1*4))", thus obtaining the new formula:
>>> =if(E1>5,A1*B1+C1,A1*B1+(C1*4))
>>>
>>> Unfortunately, I cannot simply revise the formula in D1 and then
>>> copy/paste
>>> down Column D because not all the formulas in that column are the
>>> same...
>>> However, all the original formulas need to have the exact same strings
>>> pre-pended and appended.
>>>
>>> I tried with the =CONCATENATE() function and with the ampersand ("&"),
>>> but
>>> nothing works because Excel does not regard the original formulas as
>>> text
>>> strings. (I would get rid of the "=" sign in the original formula by
>>> also
>>> embedding the functions =LEFT() and =LEN() in the formula.)
>>>
>>> Can it be done?
>>>
>>> Thanks.
>>> --
>>> tb
>>
>> --
>>
>> Dave Peterson
>
>
Similar Threads
