I need some guidance on how or if I can do what I'm hoping to do. I created a Excel spreadsheet as part of a new responsibility at work. I have employee names listed with the number of trainings they need before they qualify for a incentive bonus. I then have worksheets broken down by each department. I created a formula to link back to the main page so that when I updated a employees training it would automatically update on the dept spreadsheet. The issue I have ran into is when I add new hires to the spreadsheet I have to go into each dept's worksheet and update the formulas. Is there a formula that I can create that will recognize when an employees info gets changed to different row/column on the main sheet? Here's an example: Employee Mary Smith Trainings needed 3 (info is in column B row 47) and formula on dept sheet links to B47. We add a new employee Connie Smith trainings needed 10 (info is now in B47) and Mary Smith is now in B48. I want Mary's info on the dept worksheet to automatically update to B48 without me having to manually do it. Is there a way to do it? A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. Hi ScarlettRae, Thanks for posting the query here and we are glad to help you regarding the Excel formula. To refresh the value of a excel call, dynamically between worksheets, you may have to include 2 formula to achieve what you are expecting. Lookup a Value And Return Cell Address In Excel. This can be achieved by using the "CELL("address") syntax. You have to enter the employee name as reference to find the cell address. For example in below user list, the look up reference will return the cell address from "Original list" based on the value selected in look up. Original_list Training_count User1 3 User2 2 User3 1 User4 9 User5 0 User6 1 User7 2 User8 3 User9 4 User10 6 User11 7 Look up Cell reference User4 $A$31 User7 $A$34 User10 $A$37 User11 $A$38 Below formula used to find cell reference: =CELL("address",INDEX($A$28:$A$38,MATCH(A41,$A$28:$A$38,0))) =CELL("address",INDEX($A$28:$A$38,MATCH(A42,$A$28:$A$38,0))) =CELL("address",INDEX($A$28:$A$38,MATCH(A43,$A$28:$A$38,0))) =CELL("address",INDEX($A$28:$A$38,MATCH(A44,$A$28:$A$38,0))) By using this, you no need to worry about the dynamic list of employees. You can always add employee name to the look up and list and using the formula it will return the original cell location in this sheet. And this cell reference can be used to each department worksheets. If you need additional info, kindly look into this blog - https://www.extendoffice.com/documents/excel/2609-excel-lookup-return-cell-address.html Feel free to get back if you have any questions. --If the reply was helpful, please don’t forget to upvote or accept as answer-- VBA change event is the answer for this. But there is a workaround if you don't want to use VBA. You can pre-populate formulas in the Excel till say 1000 rows.

=IF(Sheet1!B47="",""Sheet1!B47) This will be pre-populating your Excel sheet with blanks. But if you add something in Sheet1, then the same will be reflected in your target sheet. The same formula can be dragged to the right to populate other cells of the same row. 2 additional answers Ashish Mathur • 102.4K Reputation points • Volunteer Moderator 2022-07-03T08:33:00+00:00 Hi, The FILTER() function should work fine here. If you need further help, share the link from where i can download your file. 0 comments No comments The FILTER() function should work fine here. If you need further help, share the link from where i can download your file. Anonymous 2022-07-03T06:43:15+00:00 Yes, you probably could use a PivotTable to do the report for you. Unfortunately I am not smart enough to beat Excel into submission. . The idea is something like this: Create an employee table listing employee name (ID), Department and required number of training for bonus . Then create a Training Table listing employee name, training name, training date. . Load the 2 tables into PowerQuery and the Data Model . (this is where I start to have problems) First create a column that counts the number of training entries for each employee Convert that count to text Create a pivot table showing department, emp name, required number training, and count of training. I'm having a problem displaying the count of training as text, so the PivotTable is insisting on doing a calculation . Another approach would be to create a "measure" or calculated field that creates a true/false "bonus flag" by comparing count required and count actual to set the flag. . Hopefully one of the other volunteers will pick up the ball from here. . 0 comments No comments Yes, you probably could use a PivotTable to do the report for you. Unfortunately I am not smart enough to beat Excel into submission. The idea is something like this: Create an employee table listing employee name (ID), Department and required number of training for bonus Then create a Training Table listing employee name, training name, training date. Load the 2 tables into PowerQuery and the Data Model (this is where I start to have problems) First create a column that counts the number of training entries for each employee Convert that count to text Create a pivot table showing department, emp name, required number training, and count of training. I'm having a problem displaying the count of training as text, so the PivotTable is insisting on doing a calculation Another approach would be to create a "measure" or calculated field that creates a true/false "bonus flag" by comparing count required and count actual to set the flag. Hopefully one of the other volunteers will pick up the ball from here.