Solved

Excel Question

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

Please reference Excel Q
thanks in advance

Chinglin
0
Comment
Question by:chinglinli
  • 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now