How to change the formulas in multiple sheets

[velu130468]

Hai Everybody,

I had created the formulas for a workbook with more than 30 sheets and 100 lines per sheet. Now I want to make changes in the formula. Is there any methods available for changing the same easily.

Note : All the formulas are created with Dollar symbol.

Thanks
R.Vadivelan
[email protected]

[dilipandey]

Hi Velu,

Welcome to the forum.

You can use Ctrl + H (find and replace) feature of excel where you can search in formulas with in entire workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[velu130468]

Dear Mr. Dili,

Thanks for the reply. I had tried for Ctrl+H but no use. Herewith I had enclosed the sample file which contains 2 sheets. If you go sheet 1, column S contains formula with $ Symbol, Sheet 2 contains similar formula with $ Symbol. Now I want to make some changes in formula. How can I do it. (My actual excel file contains 27 Sheets and more than 150 lines per sheet)

Thanks
R.Vadivelan

[dilipandey]

Hi velu,


Sheet1, column S has the following formula:-

=IF($P11>1,$P11*$I11,$P11*$L11)

Sheet 2, column S has the following formula:-

=IF($P14>1,$P14*$I14,$P14*$L14)

now.. what you need to change?

Regards,
DILIPandey

<click on below 'star' if this helps>

[velu130468]

Dear Mr. Dilli,

I had made this formula for calculating the total invoiced amount as on date for my client. This formula what I had put is works fine for 90% but however in some cases it is showing the wrong values. I am still trouble shooting the problem, but for time being I had submitted the sheet to my client with the above formula. Now while trouble shooting the problem, I had found a formula like this

=IF($P14>1,$P14,$P14*$L14)

So I have to change this entire formulas in the workbook as mentioned above. Or If you can help us with a formula to solve my problem then it will be most useful.

My problem is I have a columns named Quantity, Unit rate, Total Rate. If I enter the value in Percentage in Billed column then my result should be Billed Column (Percentage) X Total Rate. And if I enter the value in Numbers in Billed column then my result should be Billed Column (Numbers) X Unit Rate

Awaiting for a favorable reply

Thanks
R.Vadivelan
[email protected]

[dilipandey]

Hi Velu,

To replace anything in formula OR to edit formula, select the formula range (or if it is entire workbook, then select any one formula), press Ctrl +H.. now if you, as an example, want to replace $P14*$L14 with $X19*$Y20.. type first one in "find what" field and second one in "replace with" field and press button "Replace All".. and it's done. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[velu130468]

Dear Mr. Dilli,

Thank you for your advice. Replacing formula in one row is possible as per your method. But my requirement is to change all the formulas.

Say Column 14 contains $P14*$L14
But Column 15 contains $P15*$L15
But Column 16 contains $P16*$L16

Hope I had clarified by problem. By the way could you suggest anything for formula change ?

Thanks
R.Vadivelan
[email protected]

[dilipandey]

Hi Velu,

Replacing formula in one row is possible as per your method. But my requirement is to change all the formulas.

It's no like that.. rather I have said below:-

To replace anything in formula OR to edit formula, select the formula range (or if it is entire workbook, then select any one formula), press Ctrl +H..

so you can replace entire workbook's formula.. as per your example.. column 14 contains $P14*$L14..
not what you need to replace.. I can replace $P14 to $L14.. to do that... I'll select the entire column 14 and press ctrl + H - rest process, I have given in post#6. thanks.

regards,
DILIPandey

<click on below 'star' if this helps>