| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking cells between sheets
Example: Sheet1 has master data which is linked to various other 'working'
sheets in the same workbook. In practice, the master data has to be moved around regularly, using cut and paste. Each time this happens, Excel moves the reference in the 'working' sheet, which is not what is required. All I want to do (for example) is make sure that cell A8 in the working sheet ALWAYS equals cell A2 in the master sheet - even if I have cut and pasted the data in A2 on the master sheet to another location. I have tried =Data!A2 and =Data!$A$2 to no avail. Is there any way to 'lock' the referenced cell (Working A8) to the master cell (A2). Help much appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Linking cells between sheets
Try =INDEX(Data!$1:$65536,2,1)
or =INDIRECT("Data!A2") The first one has the advantage that it is nonvolatile and will still work if the data sheet is renamed. On Mar 11, 5:59 pm, John wrote: Example: Sheet1 has master data which is linked to various other 'working' sheets in the same workbook. In practice, the master data has to be moved around regularly, using cut and paste. Each time this happens, Excel moves the reference in the 'working' sheet, which is not what is required. All I want to do (for example) is make sure that cell A8 in the working sheet ALWAYS equals cell A2 in the master sheet - even if I have cut and pasted the data in A2 on the master sheet to another location. I have tried =Data!A2 and =Data!$A$2 to no avail. Is there any way to 'lock' the referenced cell (Working A8) to the master cell (A2). Help much appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Linking cells between sheets
=INDIRECT("Data!" & "A" & "2")
will always pickup cell A2 on sheet Date, no matter what row/columns have been inserted/deleted. -- Gary''s Student gsnu200710 "John" wrote: Example: Sheet1 has master data which is linked to various other 'working' sheets in the same workbook. In practice, the master data has to be moved around regularly, using cut and paste. Each time this happens, Excel moves the reference in the 'working' sheet, which is not what is required. All I want to do (for example) is make sure that cell A8 in the working sheet ALWAYS equals cell A2 in the master sheet - even if I have cut and pasted the data in A2 on the master sheet to another location. I have tried =Data!A2 and =Data!$A$2 to no avail. Is there any way to 'lock' the referenced cell (Working A8) to the master cell (A2). Help much appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Linking cells between sheets
Why not =INDIRECT("Data!A2") ?
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Gary''s Student" wrote in message ... =INDIRECT("Data!" & "A" & "2") will always pickup cell A2 on sheet Date, no matter what row/columns have been inserted/deleted. -- Gary''s Student gsnu200710 "John" wrote: Example: Sheet1 has master data which is linked to various other 'working' sheets in the same workbook. In practice, the master data has to be moved around regularly, using cut and paste. Each time this happens, Excel moves the reference in the 'working' sheet, which is not what is required. All I want to do (for example) is make sure that cell A8 in the working sheet ALWAYS equals cell A2 in the master sheet - even if I have cut and pasted the data in A2 on the master sheet to another location. I have tried =Data!A2 and =Data!$A$2 to no avail. Is there any way to 'lock' the referenced cell (Working A8) to the master cell (A2). Help much appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Linking cells between sheets
Very good. Thanks very much.
"Gary''s Student" wrote: =INDIRECT("Data!" & "A" & "2") will always pickup cell A2 on sheet Date, no matter what row/columns have been inserted/deleted. -- Gary''s Student gsnu200710 "John" wrote: Example: Sheet1 has master data which is linked to various other 'working' sheets in the same workbook. In practice, the master data has to be moved around regularly, using cut and paste. Each time this happens, Excel moves the reference in the 'working' sheet, which is not what is required. All I want to do (for example) is make sure that cell A8 in the working sheet ALWAYS equals cell A2 in the master sheet - even if I have cut and pasted the data in A2 on the master sheet to another location. I have tried =Data!A2 and =Data!$A$2 to no avail. Is there any way to 'lock' the referenced cell (Working A8) to the master cell (A2). Help much appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking cells between sheets | Excel Worksheet Functions | |||
| Linking chart cells between sheets | Excel Discussion (Misc queries) | |||
| Linking Sheets | Links and Linking in Excel | |||
| Linking 2 sheets | Excel Discussion (Misc queries) | |||
| Linking sheets | Excel Discussion (Misc queries) | |||
Linear Mode
