FORMULA TO DELETE A ROW IN LINKED SHEETS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
Back
Top