Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Links / January 2007

Tip: Looking for answers? Try searching our database.

Adding Row in Linked Data

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
holmesoz - 11 Jan 2007 06:07 GMT
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?
Max - 11 Jan 2007 13:12 GMT
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.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.