Is it possible to copy an excel column into another sheet including data, formatting, and all and have it update when the original changes?

Question

I have a sheet in excel that has 40 rows, with some information in each row,

A           B
Nate        32
Bob         28
Cheryl      65
John        34
Candy       23

I have manually highlighted men in blue and women in pink. Now I would like to keep one sheet with this info updated, but I have other sheets where I want to have additional info in columns B. So In my next sheet I would like to have

A           B
Nate        Smith
Bob         Smith
Cheryl      Flores
John        Candy
Candy       --

The kicker, is that if I change Nate to Mary and format it pink in Sheet1 I want Sheet2 to reflect this, is it possible? I can get the data from the cell easily enough

Sheet1!A1

But this only gets the data, is there a way to copy the custom formatting (background color, borders etc)? I would rather have these things on different sheets to avoid horizontal scrolling like mad but if I can't I will go that route.

Solution 1

Instead of moving your data to multiple sheets, you could try if freezing the 1st column (or more) would solve your problem. The frozen columns won't scroll and will stay always on the screen.

To do this, you drag the small icon next to the right of the horizontal scrollbar to the end of the column you want to freeze.

Next you select the menu Exibition and there is an icon to freeze panes.

OBS: On office 2010, if you want to freeze only 1st column, you can do it directly on the same menu without draging the small icon before.

If you are using Office 2003, drag the icon and use menu Windows -> freeze

You can freeze lines the same way (using the icon on the top of the vertical bar)

Solution 2

This is possible with some VB.

Source: http://www.mrexcel.com/forum/excel-questions/55333-easy-question-re-copying-formatting-between-worksheets.html

Right click the sheet2 name below your display then view code and delete or clear any data in the view code and write this:

Private Sub Worksheet_Activate()
    Sheets("Sheet1").Columns("A:A").Copy
    Application.ScreenUpdating = False
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("a1").Select
    Application.ScreenUpdating = True
End Sub

Comments

[laurent]

Wouldn't freezing the 1st column (so it is always displayed) on a single sheet solve your problem better than having a lot of separate worksheets?

[Nate]

I'm open to alternate solutions, how would I do this?

[laurent]

I answered with a quick guide on how to do this. I think this is the best way to deal with large number of columns or lines.