Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete sheets from a workbook without creating linked formulae | Excel Discussion (Misc queries) | |||
Linked sheets | Excel Discussion (Misc queries) | |||
Keep cells in two different workbooks linked after delete/insert r | Excel Worksheet Functions | |||
Dragging formula linked to diff sheets | Excel Discussion (Misc queries) | |||
linked sheets | Excel Discussion (Misc queries) |