Link to external sheet

[MarcoZ1982]

Hello to all,

having regard to the simplicity of removing security passwords to an excel file, I am looking for a system to link a price matrix to a configurator so that the user can use it without seeing price matrices

Example:

The cells of the Foglio1 (configurator) are linked to Leaflet 2 (matrix). I would like to be able to give the excel file with only the Foglio1 and keep the matrix with the prices, online; this to avoid that the customer does not see the prices of the matrix and I can modify them at will, when necessary. Online space is not a problem, I evaluate several solutions

[#2]

@MarcoZ1982 

To achieve the desired functionality of linking a price matrix from an external sheet to a configurator while keeping the price matrix separate and hidden from the user, you can utilize the following approach:

1. Store the price matrix in a separate Excel file or an online storage platform, such as OneDrive or Google Drive. Make sure the file is accessible to you for updates.
2. In the configurator sheet (Foglio1), create the necessary input cells and formulas that reference the price matrix.
3. Link the configurator sheet to the price matrix file using an external reference. To do this, follow these steps: a. Open both the configurator sheet (Foglio1) and the price matrix file (Leaflet 2). b. In the configurator sheet, select the cell where you want to link the data from the price matrix. c. Enter the equal sign (=) in the formula bar. d. Switch to the price matrix file (Leaflet 2) and select the cell or range containing the desired price. e. Press Enter to complete the external reference formula. f. Repeat this process for all the cells in the configurator sheet that need to retrieve data from the price matrix.
4. Save and distribute the configurator sheet (Foglio1) without providing access to the price matrix file (Leaflet 2). This way, the user can only see and interact with the configurator while the price matrix remains hidden.
5. When you need to update the prices, open the price matrix file (Leaflet 2) and make the necessary changes. The linked cells in the configurator sheet (Foglio1) will automatically update to reflect the modified prices.

By separating the price matrix into a separate file and linking it to the configurator, you can maintain control over the pricing information while providing a streamlined and secure user experience.

The approach described for linking an external sheet to a configurator in Excel can be implemented in various versions of Excel, including both the desktop version and the online version. The specific steps may vary slightly depending on the version of Excel you are using, but the general concept of linking cells between different sheets or files remains consistent across versions.

It's important to note that some advanced features or specific functionalities may vary across different versions of Excel. If you encounter any discrepancies while applying the steps, consult the documentation or help resources specific to your version of Excel for more accurate instructions.

If this is not what you want to achieve in your project, I recommend including more detailed information about what you intend to do. If possible a file (without sensitive data) or photo/s with step-by-step (cell by cell) instructions. In addition, information about the available Excel version, operating system, storage medium, etc. would be beneficial to provide a more accurate solution proposal.

[#3]

@MarcoZ1982 

If you give the link to the sheet in another workbook you actually give end user access to all data in that sheet. The only to do is copy/paste such link into blank sheet, better on same position, make relative reference in the link and drag that cell around. 

I don't think that's more reliable than password protection. All depends on end user skills.

[#4]

Dear @Sergei Baklan, thanks for your reply.

it too laborious using the solution you suggested. However, just for my test, I did a password removal without any 3rd party tools, it took like 3 minutes.. is not the reasonable security level that I need :)

[#5]

Thanks, I tried but was not working.