• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Excel Question

Dear Expert ,

Please reference Excel Q
thanks in advance

  • 2
  • 2
1 Solution
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?
chinglinliAuthor Commented:
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 ?  

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.
chinglinliAuthor Commented:
Thank you , Faustulus.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Live webcast with Pinal Dave

Pinal Dave will teach you tricks to help identify the real root cause of database problems rather than red herrings. Attendees will learn scripts that they can use in their environment to immediately figure out their performance Blame Shifters and fix them quickly.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now