Sheet with values in template

Copper Contributor

I'm making a template, which would ideally have three sheets:

Sheet 1, where the user pastes her data sheet

Sheet 2, where formulas are used to generate more data based on the pasted data from sheet 1 (protected against changes so users don't accidentally mess with the formulas)

Sheet 3, where all data from sheet 2 is pasted as values so the user can easily see the data and use filters to manipulate it (not protected against changes)

How do I make sheet 3? Is it possible to have everything in sheet 2 pasted as values in sheet 3 as part of the template setup so the user does not have to copy and paste to a new sheet? If so, how?
Many thanks in advance for your help!


1 Reply

Hi @ElineBG,

I will try to resolve your issue in short:


1. Sheet 1: Users paste their data here.

2. Sheet 2: Formulas generate data from Sheet 1. Protect it to prevent accidental changes:
- Select cells > right-click > Format Cells > Protection > Uncheck "Locked."
- Protect the sheet (Review > Protect Sheet).
Protect a worksheet - Microsoft Support

3. Sheet 3: Display data from Sheet 2:
- In cell A1, enter `=Sheet2!A1`.
- Copy and use this formula where needed.
Range.Copy method (Excel) | Microsoft Learn

The data in Sheet 3 will automatically update based on changes in Sheet 2, and users can't accidentally mess with Sheet 2's formulas.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic