Forum Discussion
#REF! error when I delete a line on master sheet
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.
Thank you! This worked perfectly for page 1 of sheet 2. How to I continue the pattern for more pages? See attached.
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.
This is exactly what I needed. Thank you very much!
