FORMULA TO DELETE A ROW IN LINKED SHEETS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Care - 26 Jun 2007 14:20 GMT

I am a new user of Excel 2003 and need to set up existing excel sheets to
link and recogonize row deletion and insertion. I have the 5 sheets linked
from 1 sheet and they are not in the same workbook. The sheets are set up
the same (ie A19=A19). Some of the rows are hidden, if that matters. I
tried some advice from previous users on thsi website, but none of the
formulas work, it tells me I am creating a circular reference. Please help.

Reply to this Message

Max - 26 Jun 2007 15:33 GMT

One way ..

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 as far as required to return the same linkages as
the former but with the added flexibility that any new row
insertions/deletions in Book2's Sheet1 (within the linked area) will now be
reflected in Book1's Sheet1. Test it out and see for yourself. Adapt the
construct similarly to link in/to other sheets.

Note that Book2.xls must be simultaneously open for OFFSET to work in
Book1.xls.

Signature

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

> I am a new user of Excel 2003 and need to set up existing excel sheets to
> link and recogonize row deletion and insertion. I have the 5 sheets linked
> from 1 sheet and they are not in the same workbook. The sheets are set up
> the same (ie A19=A19). Some of the rows are hidden, if that matters. I
> tried some advice from previous users on thsi website, but none of the
> formulas work, it tells me I am creating a circular reference. Please help.

Reply to this Message

Rate this thread: