• 544K Members
• 3,638 Online
• 648K Conversations
SOLVED

Highlighted
New Contributor

# Protection of Formulas in 2 Sheet Workbook

I have a two sheet workbook.  Sheet 1 has unprotected areas for payroll data entry, one row for each employee.  Sheet 2 is completely protected and pulls data from specific cells in sheet 1 and manipulates the data to create data for payroll checks in a required grouping of several rows on sheet 2, for each row on sheet 1.

The problem I have is that if somebody wants to take the data on a row in sheet 1, and cut and paste it to a different row on sheet 1, the links to specific cells on sheet 2 will follow the data, causing errors on sheet 2.  To further explain:

1. Assume payroll data is loaded in the first 6 rows of sheet 1, to create 6 paychecks. Each row is to create a payroll check, and the data from each row in sheet 1 is spread among 10 rows on sheet 2.
2. The person entering the data finds that row 3 on sheet 1 should not have been entered, and deletes the info .  He then wants to move rows 4 to 6 up o rows 3 to 5, and does cut and paste.
3. On sheet 2, because the data moved on sheet 1 was linked to specific cells on sheet 2, the cells on sheet 2, related to rows 3 to 6 on sheet 1,  now all reflect error message on sheet 2.

Is there a way that the Sheet 2 formulas can be locked, so that links from sheet 2 to specific cells on sheet 1 do not move and create errors when data is moved on sheet 1?  See attached workb

5 Replies
Highlighted

Highlighted
Solution

# Re: Protection of Formulas in 2 Sheet Workbook

There was no workbook attached

Generally cutting and pasting should be avoided when you have formulas linking to cells

One possible option would be to reference the cells using INDEX and a number rather than a direct link to a cell,

e.g. INDEX(A1:A10, 2)  would always reference A2 regardless of cut and paste

Highlighted

# Re: Protection of Formulas in 2 Sheet Workbook

This sounds similar to a workbook I have created to track stock and options positions. It is based on a separate worksheet that is downloaded daily from the brokerage, where the sequence of rows can change based on transactions since the last download. What I do is reference the newly updated/downloaded worksheet's cells anew each time in the workbook that actually processes the data.

Data integrity is maintained by keeping to a single row for any stock/option (in your case it would be any single employee)....

Anyway, as Wyn Hopkins as already suggested, if you could actually attach a sample of what you're working with, we might be able to make more informed recommendations.

Highlighted

# Re: Protection of Formulas in 2 Sheet Workbook

Wyn:

I tried to load my spreadsheet, but apparently that did not work.  Perhaps it was too large. It sounds like the Index function is exactly what I need.  I will try that out.  Thanks.

Tom

Highlighted

# Re: Protection of Formulas in 2 Sheet Workbook

@Wyn Hopkins I used the Array Form of the Index function, and it worked perfectly.  I set up a separate array for each column of my source data, and then copied the index function into each formula in each group  on my target sheet.  I then went through each index function on the target sheet, and only had to revise the source row number to reach the intended data.  Thanks Wyn

Related Conversations