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
79,737
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

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
Joined
Jun 12, 2014
Messages
79,737
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
79,737
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
79,737
Office Version
  1. 365
Platform
  1. Windows
Thanks for that (y)
 

Forum statistics

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