Locking cell value connected to a different workbook

[Mary Joyce Tayag]

Hello,

I am currently using excel 2010 for our weekly payroll. As of now, I have 2 workbooks. First is where I input the workers daily attendance and overtime which also computes the total salary for the week (Summary), and second is where I created a format for the pay slips that I print (Pay Slips). I was able to reference the value of the cells of the pay slips from the summary in order to save time in copying each details for each pay slip before printing. However, I need to update the list (based on the position and salary rate) weekly. So, every time I insert/delete a row on the summary, the value of the cells on the pay slips changes (e.g. Salary details of Worker 1, moves to Salary details of Worker 4 {after inserting/deleting rows}). Is there a way to fix/lock the value of the cell even if I insert/delete a row? Thank you in advance.

[#2 · Accepted solution]

Hi Mary,

Depends on how your data is structured. As variant, instead of references you may use VLOOKUP() from payslip on salaries list using Worker ID or like.

[#3]

Hi Sergei,

I am not really good at it, but I'll look it up and give it a try. Thank you.

[#4]

Hi Mary,

If you attach small sample file without sensitive information it'll be easier to help with formula

[#5]

Hi Sergei,

I just finished watching a tutorial about the basic use of VLOOKUP, tried it and it worked! I know it would take me a little time in updating the Pay Slip Sheet but it's gonna be worth it once I'm done. Thank you so much for your help.