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