Linking cells and keeping formatting

[KIST]

Hello all

I tried to find an answer to this problem but had no luck. I am using excel to timetable classes. There are five master sheets (these cannot be merged) with weekly schedules and then linked to each master sheet are subsequent worksheets for each daily schedules, one per day of the week collecting what the teaching staff is doing on each day.

My paste options as stated in knowledge knowledge base are as below suggesting it may be possible but

Keep source formatting: Preserves all original formatting of the pasted selection. Preserves all formulas as well.
Match destination formatting: Formats the pasted data to match the formatting already existing in the new location.
Values Only: This option appears only when the pasted data contains formulas. By choosing it, you strip all formulas and paste only the resulting values.
Values and Number Formatting: Formatting is preserved only for numeric values, including dates. In addition, only formula results (see "Values Only") are pasted. Text formatting is removed.
Values and Source Formatting: Strips all formulas (see "Values Only") and preserves all formatting.
Keep source column widths: Preserves the column widths as well as all formulas and formatting.
Formatting only: Pastes only the formatting, leaving all values and formulas out of the pasted range.
Link cells: Links the pasted cells to their original location. For example, if you copy A1 to B1, cell B1 will contain a link to the original location in the format "=A1". All formatting and formulas are removed.
Use Text Import Wizard: This option becomes available when you paste plain text from another program, such as Notepad. The Text Import Wizard allows you to specify how to divide pasted text into columns by using delimiter characters or defined text lengths to create individual columns.

no matter what happens I can never keep the source formatting esp colours ) when linking cells, as it says above.

In one of the master sheets I select the cell range to be linked , press ctrl C, go to target sheet select the top cell to patse in, select paste special, choose all (top left option, I am using the japanese version of excel 2003 on WIn XP Japanese OS) then paste link( bottom left hand corner) and all the data is pasted as a link but the formaating is not. If I repeat and choose formatting only it gets my desired result but won't update any future changes made to the formatting on the master sheets.

How do I link worksheets to other worksheets in the same workbook and keep all formatting?

Any suggestions appreciated.

 

[inarbeth]

Hi welcome to the board.

If you change the formatting after you have created the link, the linked cell's formatting will not update. There might be a VBA solution though you would need to keep a close eye on cell addresses and/or use named cells and named ranges to maintain the integrity of the formatting.

A workaround would be to use Conditional Formatting to change the formatting in both the original and the linked cells. If, for example you want a cell to change colour because its value exceeds a certain number that is easy to do. Simply apply the same CF to both cells.

If you want to change colours for some other reason you could use a spare cell on the first sheet to contain the relevant criterion. Thus if your spare cell is Z1, name it (this is essential because you are using different sheets). Let's call it TestCell. Use Formula Is and the conditional format would be =TestCell=1 format as blue, =TestCell=2 format as green, =TestCell=3 format as red. Use the Format Painter to copy the formatting quickly to the linked cell. To change the formatting type 1, 2 or 3 in Z1.

You are limited to three formats but this may help.

 

[ruokro8]

Has anyone found a resolution to this problem?

 

[wenona_mu10]

hi
i've just joined this forum & found a lot of the posts very helpful. not sure if my problem is similar to above. i'm currently working in excel 2003. objective is once i change the contents & cell pattern in cell A1 in sheet 1 i would like the linked cell in sheet 2 to have the same contents & pattern. getting the linked cell to have the same contents as A1 in sheet 1 is no problem but is there a formula that i can include to automate this simple task?
thanks heaps - RG

 

[loeloe]

hello all

i tried to find an answer to this problem but had no luck. I am using excel to timetable classes. There are five master sheets (these cannot be merged) with weekly schedules and then linked to each master sheet are subsequent worksheets for each daily schedules, one per day of the week collecting what the teaching staff is doing on each day.

My paste options as stated in knowledge knowledge base are as below suggesting it may be possible but

keep source formatting: Preserves all original formatting of the pasted selection. Preserves all formulas as well.
match destination formatting: formats the pasted data to match the formatting already existing in the new location.
values only: this option appears only when the pasted data contains formulas. By choosing it, you strip all formulas and paste only the resulting values.
values and number formatting: formatting is preserved only for numeric values, including dates. In addition, only formula results (see "values only") are pasted. Text formatting is removed.
Values and source formatting: Strips all formulas (see "values only") and preserves all formatting.
keep source column widths: preserves the column widths as well as all formulas and formatting.
formatting only: pastes only the formatting, leaving all values and formulas out of the pasted range.
link cells: links the pasted cells to their original location. For example, if you copy a1 to b1, cell b1 will contain a link to the original location in the format "=a1". All formatting and formulas are removed.
use text import wizard: this option becomes available when you paste plain text from another program, such as notepad. The text import wizard allows you to specify how to divide pasted text into columns by using delimiter characters or defined text lengths to create individual columns.

No matter what happens i can never keep the source formatting esp colours ) when linking cells, as it says above.

In one of the master sheets i select the cell range to be linked , press ctrl c, go to target sheet select the top cell to patse in, select paste special, choose all (top left option, i am using the japanese version of excel 2003 on win xp japanese os) then paste link( bottom left hand corner) and all the data is pasted as a link but the formaating is not. If i repeat and choose formatting only it gets my desired result but won't update any future changes made to the formatting on the master sheets.

How do i link worksheets to other worksheets in the same workbook and keep all formatting?

Any suggestions appreciated.

hey there!!
What you need to do is copy and paste the cells to the new sheet with no special copy. This will set up all of the cells with the proper format like merged cells and borders and color filled. You then highlight and delete what you just pasted. All text will disappear however the formatting will remain. Then you will repaste the desired information again and this time paste and link your data to the original. The data will fill into the proper places into the cells because the formatting is in place. So in a nutshell, copy paste delete repaste and link.