Excel fill every third row with data from 2nd sheet

FallenRider

New Member
Joined
Nov 10, 2014
Messages
4
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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 10, 2014
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 10, 2014
Messages
4

ADVERTISEMENT

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

Cheers!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows
Glad to help!

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,769
Messages
5,597,991
Members
414,201
Latest member
FJPunisher

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