Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to mirror or replicate a cell range from one worksheet to another?

For example:

----------------------------------------------
| CELL01 | CELL02 | CELL03 | CELL04 | CELL05 |
----------------------------------------------
----------------------------------------------
| CELL06 | CELL07 | CELL08 | CELL09 | CELL10 |
----------------------------------------------
----------------------------------------------
| CELL11 | CELL12 | CELL13 | CELL14 | CELL15 |
----------------------------------------------

If I wish to copy CELL01 to CELL11 inclusive to another worksheet and have the data update automatically whenever a change is made in the original, how would I go about this?

IMPORTANT: My particular dataset is rather more complex than this and consists of some merged cells, plus cells which are formatted very differently to one another (i.e. alignment, background colours, etc). I am aware of the Paste Link option, but this isn't exactly what I need; it works to a degree, but encounters problems in areas such as merged cells, for example, entering a 0 into the cells which make up the rest of each merged cell.

Many thanks.

share|improve this question

1 Answer 1

To mirror the data, I would just use ='C:\pathname\[workbookname.xlsx]sheetname'!$D$10.

If you have to mirror the layout repeatedly, then you should move the whole functionalty into VBA, otherwise, I would just replicate it once.

share|improve this answer
    
I have tried this approach, but it doesn't work out exactly how I want it; as I said, I have a rather complex dataset which uses filters. I wish to have the copied data (new worksheet) replicate the behaviour of the original data (original worksheet). For example: if a filter is applied to the original worksheet, the new worksheet will also replicate this, but only for the particular range of specified cells and not the complete worksheet in its entirety. Maybe VBA is the way forward with this one... –  SnookerFan Nov 8 '12 at 13:41
    
you don't share your complex dataset - so it is hard to advise you in detail. But the main question is - what changes and how often? If your environment (filters, colspans, etc.) changes frequently, you'll need VBA - otherwise you might get away with just setting it up one time correctly. A good bridge tech might be using NAMES, they can give you some dynamics, but most importantly, they can provide you an interface. –  Jook Nov 8 '12 at 13:48
    
The dataset is, unfortunately, strictly confidential, and requires far too much dummy data to be able to provide a more suitable example with. I would like the copied data to mirror the original data precisely - filters, formatting, etc. It looks like VBA will be the only way, after all. –  SnookerFan Nov 8 '12 at 14:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.