链接来自2个不同excel文件的单元格，但是如果仅通过电子邮件发送一个文件，则保持数字不变? [英] Linking cells from 2 different excel files, but then keeping figures if emailing only one file?

问题描述

我想知道是否有人可以提供帮助?

I'm wondering if anybody can help?

我有大量的Excel文件.这些是佣金声明，有的从美元转换为英镑，从英镑转换为欧元等.

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.

我当时想，如果我可以将所有这些语句链接到一个包含转换率的Excel文件，那就太好了.然后，我可以更新此单一货币转换文件，所有其他文件都将更新.

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!

推荐答案

尽管这将包括费率的COPY，但它是可更新的，并且仍然可以链接到费率源，但并不完全依赖于费率链接进行计算.

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.

这可能需要将货币汇率表设置为您自己的ODBC来源，但是您将可以控制所应用的汇率.将此外部数据查询结果表隐藏在工作簿中，并链接到这些单元格作为您的计算来源.

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.

这篇关于链接来自2个不同excel文件的单元格，但是如果仅通过电子邮件发送一个文件，则保持数字不变?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！