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

mickyd67

Board Regular
Joined
Jul 13, 2011
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Thanks for that. Do you need to put any values int the blank cells, or will they remain blank?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 13, 2011
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 13, 2011
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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 (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Thanks for that (y)
 

Forum statistics

Threads
1,176,089
Messages
5,901,335
Members
434,886
Latest member
qazibelal

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