creating mirror images

Question

Hello,

I have two worksheets in Excel 2010. 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

Answer 1

• Select the range of Sheet1
• Copy it
• Now go to Sheet2 and click a cell where you want mirror image
• As per screen shot, go to Paste drop down
• As Picture drop down
• select Paste picture link

Answer 2

Do you really mean "mirror image" - one in which things on the left become things on the right?

Or do you just want an "exact image".

To have a1 in Sheet2 display the same as A1 in Sheet1 use =Sheet1!A1

If the sheet name has spaces (say ABC 123), we need some single quotes, then use ='ABC 123"!A1

best wishes

Answer 3

Use following formula in cell A1 of sheet 2 and drag down and right for relevant range -

=IF(Sheet1!A1="","",Sheet1!A1)

Don't use = Sheet1!A1 otherwise your sheet will be filled with 0 values in case of blank cells in Sheet1.

Answer 4

Hi,

try this change event

source sheet is sheet1

target sheet is sheet2

right click on sheet1 tab, select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Set ws = Sheets("Sheet2") '<< change name

If Not Intersect(Target, Cells) Is Nothing Then

Target.Copy ws.Range(Target.Address)

End If

End Sub

Answer 5

The easiest way to do this via formulas requires that the size of your range be stable, so let's say you want to mirror columns A:H

Column(range) will give you a column number, so A:H will be columns 1, 2, 3, ....8

On your destination sheet, you want column A to contain the data from H on your source sheet, so you could do something like

=Sheet1!H1

but that isn't dynamic.

Instead, so something like

=offset(Sheet1!$H1,0,(column(Sheet2!A1)-1)*-1)

so basically, as you drag this formula across from A to H on your destination sheet, it should evaluate to

=offset(Sheet1!$H1,0,(1-1)*-1)= offset(Sheet1!$H1,0,0)

then column B will be

=offset(Sheet1!$H1,0,(2-1)*-1)= offset(Sheet1!$H1,0,-1)

etc

So the offset increases by one for each column you drag to the right.

HTH