Oct 26 2019 08:51 PM
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:
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
Oct 27 2019 01:38 AM
Oct 27 2019 04:48 AM
Solution
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
Oct 27 2019 06:39 AM
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.
Oct 27 2019 07:29 AM
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
Oct 29 2019 11:57 AM
@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
Oct 27 2019 04:48 AM
Solution
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