Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
I have data linked between 2 worksheets in the same workbook. When I add a
row on the one worksheet, it does not add a row on the linked worksheet - what can I do? |
#2
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Assuming Book2.xls is simultaneously open
In Book1.xls, instead of using this kind of normal link formula in say Sheet1's A1: =IF([Book2]Sheet1!A1="","",[Book2]Sheet1!A1) with A1 copied across and down use instead in A1: =IF(OFFSET([Book2]Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET([Book2]Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)) Copy A1 across and down to return the same linkages as the former but with the added flexibility that any new row insertions in Book2's Sheet1 (within the linked area) will now be reflected. Test it out and see for yourself. Note that Book2 must be simultaneously open for OFFSET to work. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "holmesoz" wrote in message ... I have data linked between 2 worksheets in the same workbook. When I add a row on the one worksheet, it does not add a row on the linked worksheet - what can I do? |
#3
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
I am a bit confused - I linked the data using Copy, Paste & then chosing Link
Cells under Paste Options. When I click on a cell on the Linked Data sheet, the cell reference is "Activity!A1", and so on for each cell. I did not use a formula. The linked data is all in the same Book - just linked between two Sheets. "Max" wrote: Assuming Book2.xls is simultaneously open In Book1.xls, instead of using this kind of normal link formula in say Sheet1's A1: =IF([Book2]Sheet1!A1="","",[Book2]Sheet1!A1) with A1 copied across and down use instead in A1: =IF(OFFSET([Book2]Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET([Book2]Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)) Copy A1 across and down to return the same linkages as the former but with the added flexibility that any new row insertions in Book2's Sheet1 (within the linked area) will now be reflected. Test it out and see for yourself. Note that Book2 must be simultaneously open for OFFSET to work. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "holmesoz" wrote in message ... I have data linked between 2 worksheets in the same workbook. When I add a row on the one worksheet, it does not add a row on the linked worksheet - what can I do? |
#4
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Apologies, I had mis-read earlier ..
It's simpler if it's just between sheets in the same book In Sheet2, Instead of using simple link formulas in A1: =Sheet1!A1 with A1 then copied across / down Use instead in A1: =IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)) Then just copy A1 across / down as required. This returns the same kind of links as the former but with the added flexibility that any new row insertions in Sheet1 (within the linked area) will now be reflected in Sheet2. Test it out and see for yourself. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "holmesoz" wrote in message ... I am a bit confused - I linked the data using Copy, Paste & then chosing Link Cells under Paste Options. When I click on a cell on the Linked Data sheet, the cell reference is "Activity!A1", and so on for each cell. I did not use a formula. The linked data is all in the same Book - just linked between two Sheets. |
#5
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Thank you that worked brillantly!
"Max" wrote: Apologies, I had mis-read earlier .. It's simpler if it's just between sheets in the same book In Sheet2, Instead of using simple link formulas in A1: =Sheet1!A1 with A1 then copied across / down Use instead in A1: =IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)) Then just copy A1 across / down as required. This returns the same kind of links as the former but with the added flexibility that any new row insertions in Sheet1 (within the linked area) will now be reflected in Sheet2. Test it out and see for yourself. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "holmesoz" wrote in message ... I am a bit confused - I linked the data using Copy, Paste & then chosing Link Cells under Paste Options. When I click on a cell on the Linked Data sheet, the cell reference is "Activity!A1", and so on for each cell. I did not use a formula. The linked data is all in the same Book - just linked between two Sheets. |
#6
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- holmesoz wrote: Thank you that worked brillantly! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |