Input Data in Excel, Receive Results at same Form, the Calculation must be done in a separate Excel Workbook.

Question

Hello,

I have a very Big Excel File which has a lot of Formula.

at this File, I have some Input Cells and some Output Cells as Results.

Now I want to separate this Summary (Input, Output cells) from this Workbook, and create a new Workbook but still, have the Funktion.

The point is that the User has no Access to Databank (Big Excel File) can only Input the data an see the Results in Output Cells.

These Output Cells calculate the values with Formulas.

Could you please give me some Solution?

Summary :

Answer 1

Hi Hossein,

Your requirement can be done if the workbooks are saved in a computer, because the Excel desktop application support this feature. Here is the process:

1. Copy the summary from your database workbook;
2. On another new workbook, click the Home tab > click "v" under Paste > Paste Link:

 

After that, the user could see the pasted contents from the database workbook even though they don't have access to the database workbook.

Please let us know if my above suggestion could help. If not, please post back and let us further assist you.

Best Regards,

Nathan

Answer 2

Hi Nathan,

It doesn't wort.

I did it (Paste with Link) when I change the Input file in Database Excel File it will change in pasted Summary Excel File, but when I change the input data in Pasted Excel File I doesn't change at Output Cells in pasted File.

Answer 3

Hi Hossein,

As your workbooks are stored in SharePoint, please note that in SharePoint, we couldn't link two deperate workbooks, but we can link two spreadsheets of the same workbook. Here is the process:

1. Open the workbook on your desktop Excel application;
2. Make Sheet1 the database sheet;
3. Make Sheet2 the form sheet;
4. Copy the summary from Shee1 to Sheet2 by Paste Link;
5. Right click on Sheet1 > Hide;
6. Click Review > Protect Workbook > select Structure > add a password > OK.

After that, other uses can only see Sheet 2, unless you unprotect the workbook with the password on the desktop Excel application.

Please let us know if my  above suggestion could help.

Best Regards,

Nathan

Answer 4

Hi Nathan,

Thanks for your Answer.

I have to find a way that links these two separate Workbook to each other, actually, I am not supposed to use the database Excel file as a separate sheet in that Form file. (even as a protected file with a password!)

what about I link these two workbooks offline and put the Form file online in SharePoint, is it work in this case? 

Best Regards

Hossein

Answer 5

Hi Hossein,

Thank you for your reply. Based on my test, by using the following steps, the contents by Paste Link will update according to the other linked workbook. However, as this process relies on the support of the desktop application and the syncing process, there are two limitations:

a. Both these two workbooks need to be opened by the desktop Excel application on your PC;

b. The change will be updated immediately on your desktop, but it may need some time for the change being synced to the workbook in SharePoint Online.

Here are the steps based on my test:

1. Store the two workbook in SharePoint Online;
2. Open the document library > click the Sync button > click "Copy library ID" and sign in to sync the document library to your PC;
3. Open both the workbooks with Excel application on your PC;
4. Use Paste Link to paste the contents from your database workbook to your form workbook;
5. Update in your database workbook, the linked contents in form workbook will be updated immediately, but it will take some time before the change is synced to the workbook in SharePoint Online.

By the way, since this is related to the limitation of external link on Excel in browser, you may try syncing the workbooks to your PC, and then use VBA to automatically copy from the database workbook and paste to the form workbook as plain values. 

Please let us know if you have any further concerns.

Best Regards,

Nathan