SOLVED

Protection of Formulas in 2 Sheet Workbook

Copper Contributor

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
Don't see your workbook. Please try to attach it again.
best response confirmed by tfalik (Copper Contributor)
Solution

@tfalik 

 

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

 

 

 

 

@tfalik 

 

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.

@Wyn Hopkins  

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

@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

1 best response

Accepted Solutions
best response confirmed by tfalik (Copper Contributor)
Solution

@tfalik 

 

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

 

 

 

 

View solution in original post