Changing data source after copying a previous row's set of reference formulas to a new row

[scootty83]

I have a workbook that is built to consolidate and calculate data from about 30 individual workbooks. Each of the source workbooks are, for the most part, identical. Most of the cells in the consolidating workbook use a ='[source workbook]sheet1'!$f$41 formula to pull the referenced cell data (other formulas calculate a percentage from two cells from the source workbook or averages from several cells in the source workbook). The source workbook data is all over the worksheet while the consolidating workbook has each source workbook represented in its own row with the like data from the other workbooks in columns.

Is there a way after copying and pasting the formulas to the next row to then go and change the source workbook for each row without going to each individual cell and changing the name of the source by hand? 30 rows x 30 columns= 900 cells that'd need to be changed. I've gone to Data>Edit Links, but this only shows currently linked workbooks and I don't see a way to link to a new unlisted workbook. Thanks!


Office for Mac 2016

[Pete_UK]

If the layout is exactly the same in the other workbooks, AND if they are both in the same folder and thus have the same path, then you could select all the cells with the formula in and do CTRL-H (Find and Replace), and:

Find what: [source_workbook_1.xlsx]
Replace with: [source_workbook_2.xlsx]
Click Replace All

Obviously, you should use the appropriate filenames.

Hope this helps.

Pete

[scootty83]

Brilliant! This worked perfectly! I don't know why I didn't think of that... Derp...

Thanks, Pete!

-Taylor

[Pete_UK]

You're welcome, Taylor - thanks for the rep.

Pete