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]

if you test =C8-D8 and it shows error because you change format cells ( at column C or D) to Text and excel don't recognize it at number.
if not please show your C8 & D8 numbers and formula that you used for see result and it show #Value Error.

and if your formula correct See this Page and change your windows setting:
How to correct a #VALUE! error

i don't like say it, but before i ask you send a formula that not show empty cells, everything ok, after that this happened and i don't know why still show zero in some cells, why this not end...

 

[maabadi]

I don't Understand How it is occured? the Only way is you Use Xl2BB (download it from up of new message bar) and upload your 0 rows with source formula to I check it?

 

[Unexpc]

I not see that, if you want check my worksheet , i upload and send a link

 

[Unexpc]

please check this

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

 

[maabadi]

I told you change E8 formula from =SUM(C8+D8) to =SUM(C8:D8) (also you can change it to =C8+D8 )
change E9 formula from =SUM(E8+C9)+D9 to =SUM(E8,C9,D9) and drag it down. (also you can change it to =E8+C9+D9 )
But you didn't.

In All of Column C & D (for Example) C40 at my formula , your Sum formula changed to SUM(#REF!) , please update it at C40 and then drag it first down and then up.
also you can replace it with
if your sum formula is SUM($C$8:C39) it should be changed to IF(ROW()<9,"",SUM($C$8:C39)-INDIRECT("C"&ROW()-34)) because I think you don't want to sum for example C40 plus another Data at column C and in Cell C74 you want sum of C8:C39 + C41:C73 then this is complete formula (Change C to D for column D):

=IF(MOD(ROW()-6,34)=0,IF(ROW()<9,"",SUM($C$8:C39)-INDIRECT("C"&ROW()-34)),IF(MOD(ROW()-7,34)=0,"Your Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))

 

[Unexpc]

yes you right this problem for sum(ref) but still if you see, some of cells show zero, what things make this zero? (in main cells empty and in this link cells (some of them) show zero!

 

[maabadi]

in the end of third row of formula ROW()-8 changed to ROW()-5 modify it to show blank result. this is modified version:
=IF(MOD(ROW()-6,34)=0,IF(ROW()<9,"",SUM($C$8:C39)-INDIRECT("C"&ROW()-34)),IF(MOD(ROW()-7,34)=0,"Your Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))

 

[Unexpc]

in the end of third row of formula ROW()-8 changed to ROW()-5 modify it to show blank result. this is modified version:
=IF(MOD(ROW()-6,34)=0,IF(ROW()<9,"",SUM($C$8:C39)-INDIRECT("C"&ROW()-34)),IF(MOD(ROW()-7,34)=0,"Your Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!D"&ROW()-8-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))

i change this because i want start link data from row 8 in print sheet and this formula start from row 9 and link data start range from main sheet than Row 3

 

[Unexpc]

Please say which numbers of formula that start from row 8 in print sheet and link data from row 3 at main sheet

 

[maabadi]

if Your Page setup is Like Before, Change of All ROW()-8 to ROW()-2
=IF(MOD(ROW()-6,34)=0,SUM($C$8:C33),IF(MOD(ROW()-7,34)=0,"Your Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-4-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-4-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-4-INT((ROW()-6)/34)-INT((ROW()-6)/34))<>""),("Sheet1!B"&ROW()-4-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))