Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 3
Default Adding Row in Linked Data

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   Report Post  
Posted to microsoft.public.excel.links
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding Row in Linked Data

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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 3
Default Adding Row in Linked Data

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   Report Post  
Posted to microsoft.public.excel.links
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding Row in Linked Data

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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 3
Default Adding Row in Linked Data

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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 4
Default Adding Row in Linked Data

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
holmesoz wrote:
Thank you that worked brillantly!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Adding more source data to existing scatter plot Tom Charts and Charting in Excel 1 March 21st 05 11:03 PM
how do you prevent data from changing values when sorting linked . Cassie Excel Discussion (Misc queries) 0 March 4th 05 11:45 AM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 12:56 PM


All times are GMT +1. The time now is 09:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"