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?

 

[maabadi]

You want hide Row of empty cells completely?!!!!
then your Page setup formula not work & your header row move up on pages.

 

[Unexpc]

Actually in my formula lookup is main function fot link cells from another sheet and (iferror(indirect(....))) for empty cells that not showing 0 or anything until fill and show value of linked cells and show to this cells, this my first formula =IFERROR(LOOKUP(2,1/(Sheet1!B34<>""),Sheet1!B34),"") and your solution formula =IF(MOD(ROW()-6,34)=0,SUM(C8:C39),IF(MOD(ROW()-7,34)=0,"Your Text",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)))),"")))
i think when added ifferror at first formula, can be fixed

 

[maabadi]

Yes. I know. But your problem now is with indirect that return 0 result ant thus I approved the IFERROR Part to the last formula. ( 3 post before). this is complete formula:

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

 

[Unexpc]

you are very good, this fix but show another problem

 

[Unexpc]

and i before question is ( I HOPE) last question

 

[maabadi]

1. About Your formula, if use sum, don't use dash"-" (minus) you should remove sum and only write: =C8-D8 and ...
2. what means =Sum(C42-D42, Sheetname!E39) , Are means =C42-D42+Sheetname!E39 and Also you can Use for E43 : =C43-D43+E42
3. If you want to Skip 4 rows ( 2 Blank & 2 for your formula ) Use this (Change Red Parts with your formula):
=IF(MOD(ROW()-6,34)=0,"",IF(MOD(ROW()-7,34)=0,"",IF(MOD(ROW()-8,34)=0,C42-D42+Sheetname!E39,IF(MOD(ROW()-9,34)=0,C43-D43+E42,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)))),""))))

 

[maabadi]

For Second Question (only show numbers):
=IF(MOD(ROW()-6,34)=0,"",IF(MOD(ROW()-7,34)=0,"",IF(ISNUMBER(LOOKUP(2,1/(A:A<>""),A:A)),LOOKUP(2,1/(A:A<>""),A:A),"")))

 

[Unexpc]

1. About Your formula, if use sum, don't use dash"-" (minus) you should remove sum and only write: =C8-D8 and ...
2. what means =Sum(C42-D42, Sheetname!E39) , Are means =C42-D42+Sheetname!E39 and Also you can Use for E43 : =C43-D43+E42
3. If you want to Skip 4 rows ( 2 Blank & 2 for your formula ) Use this (Change Red Parts with your formula):
=IF(MOD(ROW()-6,34)=0,"",IF(MOD(ROW()-7,34)=0,"",IF(MOD(ROW()-8,34)=0,C42-D42+Sheetname!E39,IF(MOD(ROW()-9,34)=0,C43-D43+E42,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)))),""))))

no need skip, thanks for formula but i test and excel drag and fill correct that i want, but still showing #VALUE!
i say this thing, before i insert last formula solution for not showing empty cells, this work correctly with this simple formula but after that, it shows this error, why when you solve one my problem, a error wil be happened

 

[Unexpc]

For Second Question (only show numbers):
=IF(MOD(ROW()-6,34)=0,"",IF(MOD(ROW()-7,34)=0,"",IF(ISNUMBER(LOOKUP(2,1/(A:A<>""),A:A)),LOOKUP(2,1/(A:A<>""),A:A),"")))

like ever, works again

 

[maabadi]

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