Find/Replace multiple formula cells

[Bains]

Hi all,

I was hoping to get some advice on replacing multiple cells over a large area that contains a combined IF/SUM formula that refers to multiple worksheets.

I would like to highlight all of the cells with these formulas (which I am currently doing by highlighting the area > Go to > Special > Formulas)
And then replace all of them with an IF formula that relates directly to the cell below.

Eg. I want to replace

E38 =IF((SUM('BB 2 AM'!E39,'BB 2 PM'!E39,.........,'BB 13 PM'!E39)-('Blocks Details'!E39*18)),"X","_")

with E38 =IF(E39="","X","_")

Is there a way to write this out for multiple cells and apply to the whole area so I can inevitably delete the other worksheets referred to by the original formula?

Thanks in Advance

[mehmetcik]

Please Login or Register  to view this content.

[Pete_UK]

Yes. If you have already highlighted the cells and then done GoTo | Special | Formulas, then you should find all the cells with formulas in that area are highlighted, with the first cell treated as the active cell (slightly different coloured highlight compared with the rest). If that active cell is E38 then you can begin to type in the formula:

=IF(E39="","X","_")

but instead of pressing |Enter| you should hold down the CTRL key and then press |Enter|, and then release the CTRL key. Excel will put the equivalent of that formula in all the selected cells, making appropriate adjustments to the cell reference.

Hope this helps.

Pete

[Bains]

That's exactly what I was after. Thankyou Pete!

Also thanks for the reply Mehmetcik.

[Pete_UK]

Glad to help.

As you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete