how can I paste to a different workbook without formula refrencing the original sheet

[arunkumar.narayanan]

hi,

I have many workbooks which have 8-9 sheets each. All identical as an
e.g each work book represents a company and each sheet has some
financial data for that company.

I am adding a new sheet which has charts and formulas which refrence
other sheets in the same workbook.

I want to replicate this sheet in all other workbooks but the formulas
and charts need to refrence the sheets in the local workbook. But when
I copy and paste all the data is refrenced from the original sheet and
I have to fix every data point and this is very cumbersome and
ineffcieint. Is there some way to do this easily ?

thanks,

Arun

 

[Pete_UK]

Once you have copied/pasted, the formulae will reference back to the
original file - imagine this is called company_1.xls, then your
formulae will all have [company_1.xls]'sheet name'!cell_reference in
them.

One easy way to change them to refer to the workbook in which you have
copied them is to highlight all the cells on the graph sheet and use
Edit | Replace (CTRL-H).

Find What? [company_1.xls]
Replace With nothing

"nothing" means leave it blank, then Replace All. Obviously, you should
use the correct filename, and ensure that you include the square
brackets in the Find What field.

Hope this helps.

Pete

 

[arunkumar.narayanan]

hi pete,

thanks a lot this helps !!

Arun

 

[Pete_UK]

Thanks for feeding back, Arun.

Pete