# Excel fill every third row with data from 2nd sheet

#### FallenRider

##### New Member
I need help with issue I'm having.
I got 2 sheets, 1st sheet is the table I need to fill and 2nd one is the data. In the data part I have column A with employee number and B with names.
On the first sheet I have table that I need to fill but table is like this:

• Blank row
• Number column + Name column
• Blank row
So when I just drag data to auto fill every third row formula skips employees and I only get every third name.
What can I do to fill every third row from 2nd sheet but without skipping names?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Joe4

Welcome to the board!

We need some more specifics.
What cells does your data start on on sheet 2 (where the data comes from)?
And what is the first cell you are populating with data on sheet 1?

#### FallenRider

##### New Member
Thanks

Data on sheet 2 starts from A2 (numbers) and B2 (names)
First cell I'm populating on sheet1 is B6 (numbers), C6 (names).

#### Joe4

Enter these formulas on Sheet1:

in cell A6: =IF(MOD(ROW(),3)=0,INDIRECT("Sheet2!A" & (ROW()/3)),"")
in cell B6: =IF(MOD(ROW(),3)=0,INDIRECT("Sheet2!B" & (ROW()/3)),"")

Then copy these formulas down for all rows.

#### FallenRider

##### New Member

Wow! Thanks a lot. This saved me a lot of time.
This will be very useful in future too.

Cheers!

#### Joe4

The three key functions used are:

ROW() - this returns the current row number (of whatever cell the formula is placed in)
MOD - this returns the remainder of a division problem
INDIRECT - this is what to use to call a value from a dynamic calculated cell

The built-in Excel help files have detailed descriptions and examples of these function (and you can also find lots if you Google these functions).

Let me know if you have any specific questions on any part of the function and how it works.

#### FallenRider

##### New Member
Already checking detailed descriptions of these functions. Thanks!

Replies
7
Views
129
Replies
52
Views
1K
Replies
9
Views
122
Replies
0
Views
45
Replies
1
Views
34

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

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

### Which adblocker are you using?

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

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