Need to update

Not applicable

Hi all,


I need some guidance on how or if I can do what I'm hoping to do. I have created a Excel spreadsheet with a list of employee names, department, and tenure ranking then I added separates workbooks in the same spreadsheet that is broken down by department. I want to be able to automatically update their tenure on the dept worksheet when their tenure changes on main page. The issue is all the formulas I have found would work if I didn't have to insert new rows when new employees get hired. Is there a way to create a formula that would update even if the data moved? 


Example: Main tab   Employee Mary Smith  shipping department tenure 47 (column d row 70...formula on shipping dept worksheet would reference D70 and tenure 47) let's say we hire a new employee and when they are added to the main tab it pushed Mary Smith down to column d row 71)...I want a formula that would recognize the change and update accordingly instead of still having the dept tab linking to D70 instead of D71.

Thanks in advance for the help!

1 Reply
You need to look into LOOKUP(), VLOOKUP(), XLOOKUP() type of functions. Ideally you should use Employee ID instead of name since you may have more than 1 Mary Smith and it is easy to make spelling errors like MaryAnne vs MaryAnn or Chris vs Christy etc.. But basically you use the Employee ID on the shipping dept worksheet to 'lookup' the corresponding information on the main page. I hope that helps.