Combining References

[richols]

I am combining several separate workbooks into a single workbook. Each of the separate workbooks has only one worksheet which makes combining easy. After I combined the workbooks, I noticed that every worksheet referenced the same set of cells, in this instance unit costs for products. Before, the previous user had gone through each workbook and updated the same unit costs over and over and over. I want to have each worksheet in the new workbook reference a new worksheet that would have only the unit costs on them. That way I can update the unit costs only once and the whole workbook would be updated. However, I do not want to go through each and every formula on each worksheet and manually change the formulas to reference the new unit cost worksheet. Is there a way I can change all of the references quickly? I was thinking a copy+paste the unit costs from existing worksheets to the new worksheet, but I get reference errors when I paste over the unit costs from the last worksheet. Can anybody help?

[JBeaucaire]

CTRL-H to open the SEARCH/REPLACE feature.
Make sure advanced options are visible.
Search the whole workbook for formulas and search/replace the reference values.

[TMS]

This is a suggested approach and, as such, is untested.

It also assumes that the layout of each sheet is the same.

Having copied all the worksheets into one workbook, each with its own set of unit costs, make an extra copy of one of the sheets and rename it as "Unit Costs". Delete the contents of the other cells but don't delete columns or rows (at this stage)

Then change the Unit Costs (table entries) to refer to the same cells on the Unit Costs worksheet.

Having set up the references on one of your original sheets, copy the table to each of the other sheets.

Once all the sheets refer to the new table on the Unit Costs worksheet, you can delete rows and columns on the Unit Costs sheet to "tidy it up".

It's dirty but relatively quick. Ideally, you'd change any formulae on the original sheets to refer directly to the new table on the Unit Costs worksheet but that could be a lot of work. Having said that, it could just be a case of changing one formula and autofilling down. Difficult to say without seeing the sheets.

Regards

[richols]

Thanks to both of you. I managed to fix it with your help!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[TMS]

You're welcome. Thanks for the feedback.