Hello!
I am working on a project and wrangling with Excel's Find and Replace feature. First, here's a rundown of what I am doing:
Multiple separate workbooks are filled out by employees. Specific cells from these workbooks (about 45 from each) are feeding back in to a "master" workbook so management can review the information from all the separate workbooks on one tab. Each workbook has its own row on the master. Here's a sample of one of my forumlas from the master document, which is pulling in data from a separate source (in this case Sample Company.xlsx):
=+'T:\Team\Membership\Renewal\[Sample Company.xlsx]Implementation Plan'!$B$18
There are about 45 cells on each row, which will pull data using this formula. To save time when adding a new row to the master document, I copy and paste an entire existing row. This duplicates the data from the other workbook on two lines. I then select the row that I just created (the duplicate row) and do a find and replace. I am looking for the company name from the previous row, and replacing it with the new company. So in this example I would be looking for Sample Company.xlsx and replacing it with something like Actual Company.xlsx. The formulas remain the same; only trying to change the source document.
The problem I am having is...
Even doing a "replace all" Excel pops open a file browser window and makes me select the Actual Company.xlsx workbook. For each dang cell. I do it once, and it immediately pops up the same window again. It does this over and over until all 45 or so cells on the new row have been changed to the correct source document. This is a problem for two reasons: 45 cells on each row, and I have about 40-50 workbooks I need to track on the master. This is a TON of extra clicks and keystrokes.
The question I have is...
Is there any way to replace that section of the forumla in ALL the cells at once? A true "replace all"??
That was a long winded question but I hope it helps in clarifying my problem. Any suggestions would be greatly appreciated.
Bookmarks