Hello,



I have two worksheets. I'm wondering if there's a way to create a mirror image of sheet 1 onto sheet 2. I want this so that I don't have to retype or copy & paste everything I enter into sheet 1 onto sheet 2. Some of the values on sheet 1 is calculated using a formula. When I change the data that goes into the formula, the value changes (as expected). I would like for the new value to be automatically reflected on sheet 2. Creating a mirror image would make it so that I don't have to do ANY work on sheet 2 at all.

Thanks

Do you want sheet 2 to be a backup of sheet 1? No, let me say that better: Do you want it so that if make a mistake on sheet 1, you can copy sheet 2 back and start over from the last save point? Or are you looking for sheet 2 always and immediately to reflect changes in sheet 1?

If you want all values on sheet 1 to show up on sheet 2, then maybe the easiest way is just to have every cell on sheet 2 point to the corresponding cell on sheet 1.

If you want a backup arrangement, then it should be just a matter of copying all the formulae from sheet 1 to sheet 2 periodically—do a mass cut-and-paste, in other words.

I know this isn't a complete explanation, but I wanted to find out more about your goal first.

I'm not using sheet 2 as a backup. The idea behind making each cell of sheet 2 point to a corresponding cell in sheet 1 is correct, but I'm looking for a way to tell a whole region to do this, not cell by cell.

Simply select the source range, copy, go to the destination range and paste, using the Paste Link option. From now on, any changes to the source will be reflected in the destination.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

"Paste Links"? That's a new one to me.

...Oh, I guess it isn't. I mean, I'd never noticed that option before, but I see it does what I was thinking about suggesting: copies 'SHEETNAME'!RC to each cell in sheet 2.

Two issues with that method that you, gib, may not think are issues:

1) The formatting doesn't go with it. This is easily gotten around; all you have to do is paste Formats, too, a one-time operation.

2) Empty cells in sheet 1 are represented on sheet 2 as 0s. Maybe you don't care; it depends on what you want to do with sheet 2. If you do care, but only about its appearance, you can easily tell sheet 2 not to display 0 values.

3) I said there were two issues but I just noticed a third: In my test of the Paste Links operation, I happened to use a worksheet that has hyperlinks in it. They don't transfer, either. Again, whether this matters to you depends on what you want to do with sheet 2.

In fact, I see now that using this method copies only the result of a formula, nothing else about the cells: conditional formatting, width and height, they're all left on sheet 1. If you want a real mirror...

Well, I can think of two ways to do that but I'm not sure either one will be worth the cost:

1) Do what Paul said, cut-and-paste the whole range from sheet 1 to sheet 2, but paste all, not just links. The problem with this is that it isn't automatic; you have to do it every time you want sheet 2 to reflect new changes in sheet 1. I suspect you considered this already; the point of your original post is that you want a way for it to happen without you having to take manual action.

2) Write a VBA macro for sheet 1 that runs automatically; every time anything changes in sheet 1, the changed range is automatically copied to sheet 2. I've never tried that, but I imagine it would work. But it would probably slow down your work. Maybe not, though; you could try it.