Permanently linking Column A in many worksheets

[Tchipu]

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 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?

Put the following in column A of the 7 sheets you want to match "MainSheet"
(substitute your actual sheet name):

=INDIRECT("MainSheet!A"&ROW())

 

[Squeaky]

Hi Tchipu,

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

 

[zvkmpw]

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. ...

It's straightforward to use INDIRECT() or OFFSET() to carry forward
column A of Sheet1 to Sheet2, Sheet3, etc. However, if all the books
have "detail" columns B, C, etc., that might not solve the problem.
When a row is inserted in Sheet1, the new column A will get longer in
the other sheets, but the detail columns won't move. This isn't
correct, if I understand the problem correctly.

There's a different approach. The problem is forgetting to group the
sheets when inserting a row. (I've been there, believe me.) Instead of
trying to make everything automatic, you can put an error check in the
sheet so it's obvious when the problem happens. Then, you can "Undo"
the mistake immediately. For example, use conditional formatting to
turn everything bright orange if the number of entries in column A is
different for different sheets.