Stopping a cell changing when referenced to another worksheet after being copied

[DannyMoor]

Hi

Hope you can help, I've tried everything to get past this problem and so far have draw a blank.

I have an excel file which lists the 'up to date' selling prices of all the goods/products my company sells/manufactures called 'PRODUCT COST.xls'
then each customer will have an excel file which contains their info and produces invoices e.g 'JOE BLOGGS.xls'

In each customers excel file I want to reference each product to the 'up to date' selling prices sheet in 'PRODUCT COST.xls' so that the customer have constant correct prices if a product goes up or down in value

This I have done

so e.g
in JOE BLOGG.xls there is a sheet called 'template' which has all the products listed (e.g column A)
and in the next column quatity required (e.g column B)
and the next column the price (e.g column C)

the price cells (column C) looks like
='[PRODUCT COST.xls]Selling Prices'!$F$4
='[PRODUCT COST.xls]Selling Prices'!$F$5
='[PRODUCT COST.xls]Selling Prices'!$F$6 etc
which gets the amounts from the PRODUCT COST.xls file and has the up to date costs of the items

Basically when a customer orders something I copy the 'template' sheet and produce and invoice
this will have the right prices on.
I will rename it to something like '1999, 18.12.2013' instead of 'template'

I problem I have is, if the price changes and I produce another invoice the data will be correct on the new invoice (e.g 2001, 20.12.2013') but the values (product cost column C) will also change on the old invoice (e.g '1999, 18.12.2013'),

Once I have copied the template worksheet and renamed it I need to freeze all the values (column C) so the referenced cells don't change anyone and if I need to look back at an invoice it is the same as the one I have printed off and given to the customer.

Can this be done ???
to stop a referenced cell changing once the worksheet is copied.

Any help would be greatly appreciated

 

[BrianMH]

For a simple non vba solution copy the column and then right click, choose paste special and then values. This gets rid of the formulas and inserts the values.

 

[DannyMoor]

yes I can see how it does partly solve the issue, Thanks

But I need to copy the whole entire worksheet using that sort of solution not just a column withing the worksheet
I have been right clicking on the 'template' sheet then move or copy... then 'create a copy'
I still have to original problem when doing this
is there a way I can copy the template and get rid of the formulas and just have to values at that moment in time ????
as you stated in your solution

 

[BrianMH]

You could just select all, copy and paste special and then save as.

 

[DannyMoor]

That once again is an option, Thank you
Now problem is i'm loosing all the formatting on the new sheet
Column widths, font colours, print settings, view size of the spread sheet etc
when I have been right clicking on the 'template' sheet then move or copy... then 'create a copy'
all this info is passed on to the new sheet and the new sheet look and preforms as good as the orginal

 

[rallcorn]

Create the new worksheet the way you described, then click the square to the left of the Col "A" & above Row "1" (this will "select" the entire new worksheet). While the entire worksheet is selected, Ctrl+C (to copy everything on sheet) and Alt+E+S+V (to Paste>Special>Values everything you just copied).

 

[DannyMoor]

Think that will do it,

Thank you so much for the help

3 weeks I've been at that problem with no success,

Should be cool now I hope