Solved

Excel Question

Posted on 2013-01-31
4
232 Views
Last Modified: 2013-02-01
Dear Expert ,

Please reference Excel Q
thanks in advance

Chinglin
0
Comment
Question by:chinglinli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 38842473
There are two ways of "copying" a value from a Master (A) on the server to a Client (B) somewhere else. Either you use a reference or you use code.
A reference would be a formula in the Client workbook (B), like =Master!A1
The user of the Client (B) doesn't need to have access to the Master (A), but they must be on the same network. If the Client workbook is to be e-mailed to a place where it can't access the Master (A) changes in the Master would no longer be reflected in the Client. However, you could still use the worksheet function to get the current data from the Master to prepare the Client. Then, before e-mailing it, change the references to the Master into hard data.
Code would achieve the same result and doesn't seem to offer any advantage for that reason. Code would get the data from the Master at the time of preparing the Client, insert it into the Client workbook which could then be e-mailed.
If the Client should return to a location where it can access the Master, both code and worksheet functions could be made to update the data. Again, code offers no advantage. So, worksheet functions should do the job. Unfortunately, you have ruled out their use by saying that referencing the Master (A) won't do.

Now, if referencing won't do, and code can't do anything that referencing can't, then the job shouldn't be possible to do. However, getting data from a Master (A) to a Client (B) is definitely possible using either code or worksheet functions. Therefore, if the task an't be solved although it is solvable the fault must be with the task.
Why can't the job be done using references?
0
 

Author Comment

by:chinglinli
ID: 38842791
Hi , Faustulus

Thanks for your detail reply !
The problem was as you mentioned : they must be on the same network ,
 
File A is a Department Master file . It is referenced by several Files B -  like PR/PO .
When someone updated File A , we hope File B all sync to File A at the same time .
File B will be downloaded to user's site . User's site could not access File A . So File B can
only  contain values instead of formula .

Is it possible to solve the problem ?  

chinglin
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 38842821
I'm afraid, no. Data can't flow where no connection exists. There are ways to allow outsiders access to your server and, therefore, the master workbook too. However, it is unlikely that you would want to give outsiders access to your server.
Therefore you should think in terms of updating the workbooks you have given away. Unless these files are of considerable size it should be easier all around to replace them rather than update their contents.
0
 

Author Closing Comment

by:chinglinli
ID: 38842936
Thank you , Faustulus.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question