FORMULA TO DELETE A ROW IN LINKED SHEETS
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. |
FORMULA TO DELETE A ROW IN LINKED SHEETS
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Care" wrote: 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. |
All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com