Insert row - Formula must follow

[papou]

Hi,

I'm new and my question is probably stupid but it is driving me nuts....

In worksheet 1, I have data in cells A1 to A30. In worksheet 2 column A, I have a reference to cells in worksheet 1, column A. My formulas in worksheet 2 are therefore : =Sheet1!A1 to =Sheet1!A30. In fact, I've copied this formula up to cell A200.

What I want to do is to be able to insert a line in worksheet 1, let's say in A15, and I want this new data to be also in worksheet 2. What happens now is that, if I insert a row in A15, my formulas in worksheet 2 will not reference cell A15; my formulas goes from =Sheet1!A1 to =Sheet1!A14 and on the next cell the formula is =Sheet1!A16

How can I build my formulas so I can insert row and keep them up-to-date.

Please ask for clarification if needed.

Thanks

[oldchippy]

Select all sheets 1-30, on sheet 1 Insert a line at A15. This will insert a line on every sheet. Now ungroup your sheets. Select sheet 1 again and drag the formula in cell A15 over A16

[papou]

Actually, my users are going to be the ones to insert new rows in worksheet 1 and I don't want them to go in worksheet 2.

I'm looking for a way to do this automatically.