Change static text to dynamic text in large workbook

I'm working with a huge Excel file and I want to change some cells with static text to dynamically change based on what the user enters in another tab. Because the file is so large I'm looking for a way to make the modification using "find and replace" or some other solution rather than manually making the change to the 1000 cells that I want to modify.

Here's an example of the original and what I want it to read:

Original static cell text: Average distance between Outpatient Care site and Group-Area
Reads as: Average distance between Outpatient Care site and Group-Area

Manual modification for dynamic cell text: ="Average distance between Outpatient Care site and " &'D1 Background'!F21
Reads as: Average distance between Outpatient Care site and District
where, D1 Background!F21 is the user-entered term "District"


I have many cells that I want to change the term "Group-Area" into the locally relevant term entered by the user. In this case it's "District", in other cases it might be "State" or "Province". Is there some way to automatically make this change to dynamic text throughout the entire workbook?

Hi sanspm,
In your example, what cell is the ="Average distance between Outpatient Care site and " &'D1 Background'!F21 in? What I am trying to determine is how cell F21 relates to the cell you want it to appear in. If the original cell is in row 21 of the original tab, and each row will line by line match each other, put your formula in the original cell, then in the original cell you can simply select the cell, then place your mouse over the bottom right corner (the little square) until you see the cursor change to "+", click and hold, and drag down all 1000 rows. It may take a minute or two to scroll down, but it will work. Each row will be tied to the corresponding F cell on your D1 Background tab. If you are wanting to make the original cells tie to a single cell in 'D1 Background', then put ="Average distance between Outpatient Care site and " &'D1 Background'!F$21. The $ will lock in row 21 and not allow it to increment when you are dragging down.
Squeaky

Thanks for your reply Squeaky and sorry for my slow response. I wasn't sure how to answer your question or follow your suggestion, so I've extracted a small example of the massive workbook.

D1 has the user-entered information in the gold cells. D3 is an example of one of many tabs in which I would like to dynamically change the generic terms "Area" and "Group-Area" to the user-entered local terms. In this case, the text in cell C9 should automatically go from "Distance between Group-Area HQ/warehouse and Area HQ/warehouse" to "Distance between Province HQ/warehouse and District HQ/warehouse".

Is there any way to achieve this without having to manually change every cell with text to a formula and then adding the reference "&'D1 Background'F$21". There are many hundreds of instances, so I think that wouldn't really be feasible for me.

I would just set up a find and replace process with instructions but there are a handful of instances where "Area" should not be changed to the user-defined local term for that unit and I don't want to rely on complicated instructions for the user that would involve having to go back and change individual cells. As I write this, I wonder if it's possible to lock those individual cells that I don't want to be changed and then I just give instructions to the user to find and replace all. An inelegant solution but maybe one option?

I think you are on the right track with the find and replace option. If you can determine what causes the few locations where "Area" should not be changed and write the exception into the formula then you could find and replace across the entire workbook. In order to lock cells, you would need to protect the sheet. Find/replace will not work on a protected sheet.