How to ignore multiple cells from drag and fill or enter any formula

[Unexpc]

Hi guys

when i drag and fill, i want skip 2 rows and force not filling 2 rows (2 rows are in every page and i can't insert any formula beacuse in my workbook, all cells filling with a formula) anyway ignore this 2 rows in every page when i drag and fill? (for example i drag and fill 34 cell in 1 column and cells formula refers to a sheet cells. When i copy and paste that sheet, this 2 rows give formula that disrupting order of link cells for my page, beacuse i want this 2 specific rows 40:40,41:41 and next 34 rows ... in each page, this 2 rows should ignore from drag and fill) how solve this automate drag and fill and ignore this 2 rows?

 

[Unexpc]

and otherthing in main sheet i work, i statred in row 3, row 1&2 fill with objects. i want link from row 3 onwards...

 

[Unexpc]

And one other thing. I assume one cell above (In this formula is A7) is empty. It is not empty add one number to red part.

A7 is fill with textbox not be a number
and one other thing, in main worksheet, column A not linked to this sheet entirely,
and in this sheet that linked main sheet, columns E & H no need to drag and fill

 

[maabadi]

Use this one for A9 and drag to autofill:
=IF(MOD(ROW()-6,34)=0,"your sum formula",IF(MOD(ROW()-7,34)=0,"your Text",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")))

Replace red parts with your formula and text you want.

 

[maabadi]

and otherthing in main sheet i work, i statred in row 3, row 1&2 fill with objects. i want link from row 3 onwards...

Then, what is your simple formula for row 3 ?

 

[Unexpc]

Then, what is your simple formula for row 3 ?

Row 3 in main worksheet that linked to this sheet
I link row 3 except cell A to this sheet

Use this one for A9 and drag to autofill:
=IF(MOD(ROW()-6,34)=0,"your sum formula",IF(MOD(ROW()-7,34)=0,"your Text",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")))

Replace red parts with your formula and text you want.

Text is work but Sum not working (showing formula not number)

 

[maabadi]

add equal to your formula in quotation mark.

 

[Unexpc]

add equal to your formula in quotation mark.

Yes i tested with equal and without equal ,doesn't work

 

[maabadi]

Yes i tested with equal and without equal ,doesn't work

Sorry my fault. Use it without equal and quotation mark. Only (example):
Sum(C8:C39)
or totally insert this formula to row 40 and drag up & down:
=IF(MOD(ROW()-6,34)=0,Your SUM Formula,IF(MOD(ROW()-7,34)=0,"Your Text",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")))

 

[Unexpc]

Sorry my fault. Use it without equal and quotation mark. Only (example):
Sum(C8:C39)
or totally insert this formula to row 40 and drag up & down:
=IF(MOD(ROW()-6,34)=0,Your SUM Formula,IF(MOD(ROW()-7,34)=0,"Your Text",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")))

this development to be fix, i have another problem
this fix for sum and do correctly, just need edit to sum page 2 onwards
in page 1 this sum is used =SUM(C8:C39) but in page 2 =SUM(C42:C73,'this name sheet not sheet1'!C40) and in page 3 =SUM(C76:C107,'this name sheet not sheet1'!C74) and next... i try sum formula page 2 and 3 but it shows #NAME error, what am i do?
other thing i want start at row 8, you say how i change but in new formula i confused...
and other thing, in columns A and B i not use sum formula (sum cell is empty) and just text cell is fill.
thank you for your HARD WORKING

 

[maabadi]

Are you write your sum formula correctly. the #NAME error because of error at writing formula. and you don't need equal sign at first.
if you write total of above formula at row 40 with your sum formula SUM(C8:C39) and drag it down first when you see result at row 74 at formula bar (fx bar)
you see formula changes to SUM(C42:C73) and etc...