Permanently link Col A edits, inc 'insert row' in multiple workshe

[Tchipu]

I'm using Excel in Office 2007. I have a workbook with about 8 worksheets.
I'd like column A (a list of companies) to be linked to all worksheets, so
that any edit in one will be reflected in all. I can do this as a one-off by
linking all sheets with the cntrl button, and then making my edit. However,
this is fallible, and should I forget to link, I'll make a mess of all. I've
also tried the copy/paste special/paste link idea which works fine, until I
wish to insert a row, when it doesn't work. As I need to insert companies
frequently, I'm back to doing one-off edits using the cntrl button - no good
to me. Could anyone help with this problem? Please?

 

[Glenn]

I'm using Excel in Office 2007. I have a workbook with about 8 worksheets.
I'd like column A (a list of companies) to be linked to all worksheets, so
that any edit in one will be reflected in all. I can do this as a one-off by
linking all sheets with the cntrl button, and then making my edit. However,
this is fallible, and should I forget to link, I'll make a mess of all. I've
also tried the copy/paste special/paste link idea which works fine, until I
wish to insert a row, when it doesn't work. As I need to insert companies
frequently, I'm back to doing one-off edits using the cntrl button - no good
to me. Could anyone help with this problem? Please?

See answer to your previous post.

 

[Squeaky]

Hi Tchipu,

I responded to your other post. Here is what I said:


I know of one way, but it is time consuming to set up.

Let's use cell A1 as an example.

Under the Developers tab select the INSERT Icon then under ActiveX Controls,
select TEXT BOX and put it over cell A1.

Enter the design mode by selecting the icon next to the Insert icon.
In the text box properties (right click on box, select properties) set the
Linkedcell to SHEET1!A1, Use your tab name instead of the SHEET1, but keep
the !. Make sure to use the tab name (sheet1! in this case) and not just the
cell A1.

Right click and copy, then go to sheet 2 and paste it to A1, as well as A1
in all the sheets.

Entering a word in one text box will show in all of them.

To "mass" produce this:

On Sheet1

Right click on A1 and do a copy/paste to all the cells in column A on as
many as you need.

After you have made however many boxes you need on the first page, you have
to set the Linkedcell properies to the cell each one covers. Enter Design
Mode, select the Textbox over cell A2, then change the Linkedcell to
Sheet1!A2. Leave the properties box open and just select the next Textbox
(over A3). It's properties will display. Do that for all the boxes in column
A.

THEN:
Click on the first box in cell A1, then hold the control key down and click
on each box to select all the boxes so every one of them is selected. Then do
a copy, (right click on any of the selected boxes, select copy) then select
the next sheet (sheet 2), select cell A1 and paste all of the boxes into
column A. Repeat for as many sheets as you have. (Do not try to copy a sheet
as that will change the Linkedcell.)

Let me know if you have problems.

Squeaky