Find and replace in formulas

**naquer** · 07-19-2009, 05:56 PM

Hello

I have various formulas (such as: =(D3-$E$3)/$E$3 )
which continues for each row (IE next row 3 is replaced with 4 etc)

I have made som errors when I made the formulas and therefore I have the following questions:

1. Is it possible to find and replace all E's with another letter (F in my case). I haven't been able to resolve this, when it has to be done inside formulas?
The optimal would be if I could set 'Display formulas' under tools, and find and replace in the formulas displayed.

2. Is there a smart way of swapping D3 with E3 for all formulas? (So the result will be: =($E$3-D3)/$E$3 and etc for all formulas)

Thanks in advance.

**oldchippy** · 07-19-2009, 06:25 PM

Hi,

Something like this?

**naquer** · 07-20-2009, 10:25 AM

Of course :-), Thanks !

No idea how I overlooked that

However I want to change the order in all my formulas, so that the next row will be
=($E$4-D4)/$E$4

I have about 100 rows, so it's alot of work if i have to search and replace for each row.

I also drag the formulas the continue so that the next formula is =($E$4-E4)/$E$4, therefore removing the $ is not the solution.

**oldchippy** · 07-20-2009, 12:56 PM

Hi,

Take a look here

http://excel.tips.net/Pages/T003067_...a_Pattern.html

Does that help?

**naquer** · 07-20-2009, 01:08 PM

Thanks alot for the answer. This will for sure help me out in other situations.

However the solution to my problem showed to something as simple as this:

Answer
You need an ABSOLUTE reference which is $A$1 - which you can do by pressing the F4 key with the cursor on the A1 in the formula bar (or just type the dollars)

For reference

$A1 would always refer to column A, but variable rows
A$1 would always refer to row one but variable columns

Thanks again though!

**oldchippy** · 07-20-2009, 05:40 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save