Replace formula with result

**Scrap74** · 01-07-2010, 03:34 AM

Hi All,

Does anyone know of a way to replace a cell's formula with its result for an entire workbook?

I have a workbook that uses INDIRECT to read data from 25+ other .CSV files. It then compiles this data and builds a heap of charts.

I have found sending 25+ files to someone for them view is causing a few problems, not least because INDIRECT can be a bit troublesome on some computers.

So. similar to break links, I would like to save all the values in a copy of the workbook and email to people for review.

**teylyn** · 01-07-2010, 03:39 AM

Hi,

make a copy of the workbook
In the copy, hit Ctrl-A to select everything
hit Ctrl-C to copy that
go Edit - Paste Special - Values - OK

save the workbook copy an send off

**Scrap74** · 01-07-2010, 03:56 AM

Thanks Teylyn,

Apart from a copy/paste, is there any more automated way of converting formulas to data?

I have to do this for 6 projects (workbooks) and each workbook has up to 25 sheets.

With reviews and changes, a manual method of breaking the formula will be terribly onerous

**teylyn** · 01-07-2010, 04:04 AM

fire up the macro recorder
go through the steps outlined above
save the macro in your personal.xls
run it from the copy of the workbook

(test it on a non-critical file first

)