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

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Attachments

  • Screenshot 2020-11-14 165211.jpg
    Screenshot 2020-11-14 165211.jpg
    60.3 KB · Views: 16

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
thats fixed with before formula (in new formula, not skip correctly 2 specific row) but another in before formula, this start at Cell B6 not Cell B3!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please say which numbers of formula that start from row 8 in print sheet and link data from row 3 at main sheet

You should convert all of ROW()-8 to ROW()-5 (input this formula to Row 8 and drag it down)

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

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
You should convert all of ROW()-8 to ROW()-5 (input this formula to Row 8 and drag it down)

=IF(MOD(ROW()-6,34)=0,SUM($C$7:C7),IF(MOD(ROW()-7,34)=0,"Your Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet1!B"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet1!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
Again, you solved my problems, Thank You... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,042
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top