Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Permanently linking Column A in many worksheets
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Permanently linking Column A in many worksheets
Tchipu wrote:
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()) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Permanently linking Column A in many worksheets
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 "Tchipu" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Permanently linking Column A in many worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I format a pivot table column - permanently? | Excel Discussion (Misc queries) | |||
Permanently link just a single column between multiple sheets? | Excel Discussion (Misc queries) | |||
how do i permanently delete a row or column | Excel Discussion (Misc queries) | |||
How do you permanently hide and password protect a column? | Excel Discussion (Misc queries) | |||
Is it possible to permanently group worksheets | Excel Discussion (Misc queries) |