formula to find the data of a next cell in a list

[merrimop]

The Workbook has 2 sheets. On Sheet 1, Column A is a list of Warehouse
locations, (1A1A is in Cell A3, 1A1B is in Cell A4, 1A1C is in Cell A5 and so
on). On Sheet 2 I am setting up Lable Location Templates which span Cells
A1:K35, with A1 containing the text of Sheet1!A3 (i.e. 1A1A)
I want Sheet2!A36 to display the data from Sheet1!A4 (i.e. 1A1B) and for
each copy down the page to do likewise. I was hoping there was some kind of
NEXT type formula for finding a value in a list and then returning the next
value from said list. The only other alternative I can see is hlookup but I
might as well type in all the values as I would have to change the row value
in the formula each time for this to function correctly. Any help is
appreciated

 

[Max]

In Sheet2,

Put instead in A1:
=INDIRECT("Sheet1!A"&INT((ROW(A1)-1)/35)+3)
Copy down as far as required

The above will place the contents of: Sheet1!A3 within A1:A35
then: Sheet1!A4 within A36:A70
then: Sheet1!A4 within A71:A105
and so on ..

 

[Max]

Typo in line:

then: Sheet1!A4 within A71:A105

should have read as: