Hello,

I have a workbook where all the data is spread across multiple data worksheets. I have also built an analysis worksheet, with lots of formula-based conditional formatting, that analyzes the data in those data worksheets (i.e the conditional formatting formulas refer to the data worksheets).

Periodically, I am sent a new workbook with updated data worksheets. I've tried to copy my analysis worksheet to the new workbooks, and while it is possible to keep edit the formula links to point to the new data, it's much harder to get the conditional formatting to work when that way.

So instead, I'd like to replace the existing data worksheets in my workbook with copies of the new data worksheets from the new workbooks. Unfortunately, this also breaks the conditional formatting, which doesn't seem to know where to look for the data to determine whether formatting test are true or false.

How can I update the data worksheets and keep my lovely analysis sheet working?


Many thanks!
JayUSA