#REF! error when I delete a line on master sheet

[wuzmop]

I'm trying to create a workbook where I enter data into sheet 1, outputs in the format in sheet 2. The lines in sheet 1 need to be able to be deleted, but when I do, it breaks the link in the formula giving me the error. What function should I use to insure when I delete a row on sheet 1, the data on sheet 2 moves up to replace that deleted row?

[#2]

Hi, Try the following formula in cell Q2 for example : =IFERROR(Sheet1!A134,Q3)

[#3]

Hi,

If you delete/insert rows better not to use links. Your Sheet2 is quite good structured, if i understood it correctly you repeating in groups by 3 columns each 33 rows from first sheet.

When in A2 of Sheet2 we take the value of the A2 cell in Sheet1

=OFFSET(Sheet1!$A$1,ROW()+33*INT(COLUMN()/4)-1,0)

Latest zero is to be changed on 1 and 2 for culumns B and C accordingly (one and two columns to the right from column A).

When select cells from A2 to D2 and paste as fromulas to E2:H2, I2:L2, etc, and after that copy entire row 2 down till end of the range.

Better to use number 33 as parameter (put in some cell outside and use reference on it or name).

Final result is in attached file.

[#4]

Thank you! This worked perfectly for page 1 of sheet 2. How to I continue the pattern for more pages? See attached.

[#5]

Hi,

First, i'd remove column names for entire page from within the sheet and repeat them only for printing through page layout setup

With that assuming each page has 33 roows the formula will be

=OFFSET(Sheet1!$A$1,ROW()+33*(4*INT((ROW()-2)/33)+INT(COLUMN()/4))-1,0)

for A2, and with 1 and 2 at the end for B2 and B3. Copy that block to the right on each next 3-columns block and after that row 2 till end of the file.

See Sheet2(2) in the attached file.

If you prefer to repeat columns titles within the sheet the formula will be bit complicated, but the idea is the same.

[#6]

This is exactly what I needed. Thank you very much!