# Using = to copy info from a cell to another sheet, but the 2 sheets have different layouts

#### mickyd67

##### Board Regular
Hi All - this one has been driving me mad.

In Sheet 1 I have a list of 200 item codes. Basically running from Cell A1 to A200

In Sheet 2 I have rows of data related to each of the 200 item codes - but this data set has 9 different elements. So between each item code on the sheet there are 9 rows. Every 10th row has a new item code.

So in Cell A1 I have the formula =Sheet1!A1, In Cell A10 the formula needs to be =Sheet1!A2

However, which will come as no surprise, when I copy the formula down from the above cell it automatically comes up as: =Sheet1!A10.

Is there a way for Excel to automatically reference the next row on my source data to save me from copying the formula down and manually going into each row and changing the formula manually? I am sure there must be a way.

Thanks all

#### Fluff

##### MrExcel MVP, Moderator
Thanks for that. Do you need to put any values int the blank cells, or will they remain blank?

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

#### Fluff

##### MrExcel MVP, Moderator
If you need to put values into the blank cells, try
Excel Formula:
``=IF(MOD(ROWS(A\$8:A8)-1,14)=0,INDEX(INDEX(Sheet2!\$B:B,5):INDEX(Sheet2!\$B:\$B,5000),INT((ROWS(A\$8:A8)-1)/14)+1),"")``

#### mickyd67

##### Board Regular
If you need to put values into the blank cells, try
Excel Formula:
``=IF(MOD(ROWS(A\$8:A8)-1,14)=0,INDEX(INDEX(Sheet2!\$B:B,5):INDEX(Sheet2!\$B:\$B,5000),INT((ROWS(A\$8:A8)-1)/14)+1),"")``

HI Fluff - thank you very much. This works. You guys are great!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Also please update your profile to show what version of Excel you are using. It saves members having to ask.

#### mickyd67

##### Board Regular
You're welcome & thanks for the feedback.

Also please update your profile to show what version of Excel you are using. It saves members having to ask.

No problem - I'll do that

Thanks for that

Replies
0
Views
420
Replies
3
Views
93
Replies
12
Views
312
Replies
2
Views
91
Replies
4
Views
245

1,181,616
Messages
5,930,965
Members
436,767
Latest member
Langaws

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