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

[mickyd67]

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]

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

 

[Fluff]

If you need to put values into the blank cells, try

=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]

If you need to put values into the blank cells, try

=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]

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]

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

 

[Fluff]

Thanks for that