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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

arunkumar.narayanan@gmail.com - 27 May 2006 19:32 GMT

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

Reply to this Message

Pete_UK - 28 May 2006 00:36 GMT

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

Reply to this Message

arunkumar.narayanan@gmail.com - 28 May 2006 04:34 GMT

hi pete,

thanks a lot this helps !!

Arun

Reply to this Message

Pete_UK - 29 May 2006 11:50 GMT

Thanks for feeding back, Arun.

Pete

Reply to this Message