0

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!

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
ade123
  • 2,773
  • 8
  • 29
  • 32

3 Answers3

1

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.

datatoo
  • 2,019
  • 2
  • 21
  • 28
  • This sounds along the lines of what I am looking for. If I set this up, would I then be able to just send the one excel file in emails without messing up the rate cells? Just trying to get my head around how things would work. Would the cells in the hidden rate sheets be linking to the single file containing the rates and if so, would this not mean that if I was to only send one excel file as an email, the rates sheet would be trying to link to the rates file that is no longer there? Just getting a bit mixed up! – ade123 May 18 '11 at 10:53
  • @ade123 What you distribute would have a rate sheet (hidden) but all your formulas would use it. That hidden sheet would have a connection to the external datasource, and since that would probably only be refreshable while you had the sheet being distributed those rates would remain unchanged, unless YOU explicitly refreshed the rates. Yes you would just send a single file, and if automatic refresh is off, the rates should not change, or try to. If you are concerned about that. you could also incorporate a macro which would copy paste special values, the hidden sheet data before sending. – datatoo May 18 '11 at 14:57
  • So just to get this clear. If the external master file is not present, there won't be any errors on the cells in the hidden sheet? Would they just keep the info from the last time they had access to the master file. Is setting auto refresh to 'off' the key here? Thank you for the help so far datatoo! – ade123 May 18 '11 at 15:56
  • @ade123 That should be the case. I did this to test the result, sending an external datasource linked file to myself at a different machine, which worked fine. External data range properties allow you set the refresh control options as well. You could alternately generate your external rate table with a macro running the external data query, so you have a number of things to try for your situation. – datatoo May 18 '11 at 16:10
  • Thanks datatoo, I'll give some of this a go. – ade123 May 19 '11 at 08:29
0

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.

ASGM
  • 11,051
  • 1
  • 32
  • 53
Khalid Nisar
  • 1
0
  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
Prateekro
  • 566
  • 1
  • 6
  • 28