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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
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
56,492
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
56,492
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,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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