Excel linking

[draylan]

Hi,

I was wondering if it is possible to mirror cells from one worksheet to another... not just the values but size, format, color and everything. I know you can do a copy and paste special, but i think that only links values. A user is trying to create a color coded spreadsheet that will change on a constant basis and would like to link everything to a master sheet. Any help would be appreciated.

Thanks!

Dave

 

[AnotherHiggins]

You could right click on the sheet tab and create a copy or the entire worksheet (even to another workbook if needed).

That way all of the formatting - including Conditional Formats - are retained.

Then switch all of the values to formulaic references (something like [COLOR=blue white]=Sheet1!A1[/color] or even [COLOR=blue white]=if(isblank(Sheet1!A1),"",Sheet1!A1)[/color]).

Just drag the formulas across and down.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[draylan]

Hi John,

Thanks for your response. This would work if the colors or the cells weren't going to constantly change. I know... it's weird but thats' what they are trying to do. I would like to create an exact mirror image of that cell. So if I changed cell A1 from Sheet1 to a yellow fill with value 14... then the linked cell on Sheet2 would look exactly the same. Color and everything. Then if they decide to change the color in Sheet1 to blue, it would automatically change in Sheet2. This is a bit more complicated and on a larger scale of course.

Thanks for you help!

Dave

 

[AnotherHiggins]

Are the colors of cells really completely random?? Why would that be useful? Is there a business need for having randomly-assigned colors in cells? If the colors are random, how can they be useful in interpreting the data?

*My first suggestion:
If you want something set up so that values < 14 have a yellow background and values > 14 have a green background (or whatever values/colors), then use Conditional Formatting on both sheets.

Have a look at Format > Conditional Formatting and Excel's help file entry for Conditional Formatting.

*If you're determined to let users randomly assign colors to cells and want those changes reflected in another sheet, you'll need to use VBA.

If you choose to go that route, please create a new thread in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[draylan]

They are making timeline type data using different colors which will change constantly. Probably more trouble than it's worth. Thanks for you help though!