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]

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...

Yes i know, my problem with sum page2,3,4... that plus 34 rows behind(for example in page 2 C74+C40 and in page 3 C108+C74+C40 and etc... in this sheet not in sheet1
Except this point, everything is well

 

[maabadi]

Only you need to add $ sign to your first cell to sum. Select only the first cell Address( I think it was C8) and press F4 key. It should converted to $C$8.
write at row 40 this formula and drag first down then up. If your formula is SUM(C8:C39):

=IF(MOD(ROW()-6,34)=0,SUM($C$8:C39),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]

Only you need to add $ sign to your first cell to sum. Select only the first cell Address( I think it was C8) and press F4 key. It should converted to $C$8.
write at row 40 this formula and drag first down then up. If your formula is SUM(C8:C39):

=IF(MOD(ROW()-6,34)=0,SUM($C$8:C39),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)))),"")))

Sorry you sloved my problem, i was insert wrong sum formula that i want...
Im very thankful for your hard working and spent your time for solve my problem

 

[maabadi]

Good Luck & You're welcome. Please click on tick at right of post have correct answer.

 

[Unexpc]

Hi again
i have a few questions please check it

 

[maabadi]

1. change formula to this:

=IF(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)))),"")=0,"",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)))),""))

2. VBA don't accept other language except English. Your solution is you use VBA sheet name not Excel sheet Name and change this parts (example at image):

Sheets("پرینت") to Worksheets("sheet2")

 

[Unexpc]

1. change formula to this:

=IF(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)))),"")=0,"",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)))),""))

2. VBA don't accept other language except English. Your solution is you use VBA sheet name not Excel sheet Name and change this parts (example at image):

Sheets("پرینت") to Worksheets("sheet2")

i tested, in formula and sheet name can write but in vba cannot

 

[maabadi]

see the other post. i describe example for vba

 

[Unexpc]

i know in vba does not work, your making this formula and i say can change and insert for example persian character in formula...
and what about don't show empty cells?
sorry if i asking you, because you know what in my worksheet...

 

[Unexpc]

1. change formula to this:

=IF(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)))),"")=0,"",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)))),""))

2. VBA don't accept other language except English. Your solution is you use VBA sheet name not Excel sheet Name and change this parts (example at image):

Sheets("پرینت") to Worksheets("sheet2")

i read now my solution with vba sorry i understand now what you say,
please say what am i do for not showing empty cells?