How to mirror 3 cells across multiple sheets in a workbook?

Hello,

I have 3 cells in Sheet2 (Cells B57:B59) which contain names of people who need to be trained. These names are mirrored in the following locations:

Sheet 4 (Cells B39:B41),
Sheet 5 (Cells B36:B38),
Sheet 6 (Cells B37:B39),
Sheet 9 (Cells A25:A27)
Sheet 11 (Cells B22:B24),
Sheet 12 (Cells B36:B38),
Sheet 13 (Cells B36:B38),
Sheet 14 (Cells B22:B24),
Sheet 15 (Cells B19:B21),

Currently, I just use "=" and then find the target cell (from Sheet2), which is good if the names don't change. However, the names may need to be changed with manual input as sometimes they can't make the training, different people turn up etc.

Is it possible to have some code or formula that allows manual editing of one of those cells above and then mirrors the change across all of those worksheets?

For example, changing the name in Sheet 13 Cell B36, will change Sheet2 B57, Sheet4 B39, Sheet5 B36, Sheet6 B37, Sheet9 A25, Sheet11 B22, Sheet12 B36, Sheet14 B22 and Sheet 15 B19 automatically?

Many thanks with any help with this.

NickyHavey

By the way, I must add that after thinking about your question I wonder why you have the same data that must be replicated across multiple sheets in the first place, and can change in any one of those sheets. It suggests that you may have a data design issue and there may be a more effective solution for your overall problem. Care to share more about what you are doing? Maybe even attach a file?

Thanks for getting back so quickly.

I work in a company where we sell all sorts of machines to customers and looking at a way to standardise the process from the sale to installation of the machine.

I have been tasked with trying to create one Excel file where the information about the install (customer name, trainees etc) can be transferred across from our:

- Sales admin team, who provide the information to begin with about the 3 trainee names to our;

- Technical supervisor, who calls up the customer before the install to confirm those 3 trainee names are the same. It should also be stressed that the questions the Tech Supervisor can ask varies depending on the product that is being sold. There are 8 different tabs I've made, which I refer to in the original post, that all need to have the trainee names on. Those different tabs are basically checklists that the Tech Supervisor ticks off and confirms the name but it depends on the product sold. If the names change from when our Sales team hand over the information to the Tech Supervisor, then it needs to update the names across the file. Once the Tech Supervisor has completed this, then it gets forwarded on to the;

- installation engineer where all the updated information about trainees, the job is sent in this Excel file (so we're trying to keep it in one place)


I just tried the code and been getting "run time error 9" subscript out of range. I can't really attach the Excel spread sheet as it has sensitive data but the names of the tabs I've matched to suit the name required in the code. When it goes in to debug mode, it just highlights the whole "Rangelist" in yellow.

For reference, the code I changed for the worksheet is now:

Please Login or Register  to view this content.

Sorry about that. I mocked up a file using the names in your code and found the problem. Add the red lines as shown.

I see how you are using the file, as a workflow management tool--but are the trainee names the only thing that could change along the way?

Your current solution is probably best to keep your overall solution simple. A more comprehensive way to handle this would be to build a true application, keeping all the data in one central place then giving different people a view of the data appropriate for their different roles, using pop-up forms. But that is probably overkill if you get this solution to work well for you.

Please Login or Register  to view this content.

There are many macros I have set up currently in this Excel file (including creating a pdf of the current worksheet and opening up outlook so it can be e-mailed to various offices/the customer) and I don't know if that is interfering with the code you have written as I'm still getting that same error message pop up (with the red lines included).

I am aiming to have a complete solution at the end of the month for this and what you suggested was something I am hoping to achieve but I don't really know how to do that. One suggestion from a colleague is to have most of these tabs hidden and then they only pop up if a button is pressed. He also suggested a list might be a better option to pull data from but again, I don't know how to do that. I can send you over the Excel file privately so you can have a better look?

I apologise, I have found the error. The name of one of the tabs "Customer checklist EMOTC" actually had a space at the end of it in the name of the sheet that I didn't see. I have since managed to get the code you have written and it brought a tear to my eyes (of joy).

Thanks a lot for your help with this.

You've made one happy employee!

Best Wishes

Nick