Linking cells from 2 different excel files, but then keeping figures if emailing only one file?

I'm wondering if anybody can help?

I have a large number of Excel files. These are commission statements and some are converted from USD to UK Sterling, from UK sterling to Euros etc etc.

They are quarterly statements and at present I have to go through each file one by one and enter in the specific currency conversion rates for that month.

I was thinking it would be great if I could link all these statements to a single Excel file which contained the conversion rates. I could then update开发者_开发问答 this single currency conversion file and all the other files would update.

I think I could manage this, but there is a little problem I have ran into.

If I then send the statement files out to individuals, the local link between the two files would be lost and therefore, surely so would the conversion figures. The only fix I can see would be to send the file containing the conversion rates along with the statement, but if possible I would prefer not to do this.

I hope you can understand my problem and it would be great if anybody could suggest any fixes to this!

Although this would include a COPY of the rates, it would be update-able and still be linked for you to the rate source, yet not completely rely upon the link for the calculations.

I would recommend creating an External Database Query that connects to your file storing those values. You can set the properties of the query to NOT refresh automatically and so the user should not be prompted to do so, which might confuse them.

This may require setting your currency rate sheet as an ODBC source for yourself, but then you will have control over the rate that applies. Make this external data query result sheet hidden in the workbook and link to those cells as the source of your calculations.

Perhaps this is wrong, but it is not as if the rate itself is something you want to hide, since the results are dependent upon it. You merely want to not complicate things for the user.

I would suggest the following:

In the target sheet, select the cells, copy them, and then simply paste (Special-Values only). This works for me all the time.

1. Copy all ctrl+A ctrl+C
2. Open New sheet ctrl+N
3. Paste ctrl+V
4. In drop-down Window select VALUES Only