Excel 2000: Inserting rows in link spreadhseets

[meliorexi]

Hi

I've got two spreadsheets in the one workbook which have linked cells (ie changes in a cell on on worksheet are automatically reflected in a cell in the other worksheet).

Is there anyway inserting a row in one sheet can result in a row being automatically inserted in the corresponding sheet?

Thank, Chris

 

[macropod]

Hi Chris,

You could use an event-driven macro for this, presumably to both insert the new row target and insert a formula that references the new source row. However, if you only want the values listed on the second sheet, you might be able to use an OFFSET formula there instead. For example, the formula:
=OFFSET(Sheet2!$A$1,ROW()-1,COLUMN()-1)
in cell A1 and copied down and across as far as you're likely to need would initially produce a similar result to doing the same with the formula:
=Sheet2!A1
but would accommodate the addition and deletion of rows and columns on Sheet2, except for column 1 and row 1.

Of course, the formula approach will return 0 values, which you can suppress via Tools|Options or via:
=IF(OFFSET(Sheet2!$A$1,ROW()-1,COLUMN()-1)="","",OFFSET(Sheet2!$A$1,ROW()-1,COLUMN()-1))

Cheers