formula to bring results from other sheet in correct order

[Ramzes]

Hello,

I am creating a production progress tracker and I got stuck with displaying items in correct order due to the fact that on the main sheet I will be using it is few lines for the same material [this is so that other formulas can work].

I have attached a sample file and here comes an explanation what I am trying to do.

In the tab Sheet1 column P I have listed materials.
These materials are to be displayed [brought in] from tab ListOfOrders column A.
The first material is to be in P2 and needs to display first material from ListOfOrders tab in A6. [Please note that ListOfOrders is a pivot table in the real life].
In tab Sheet1 = P9 I need to display the second material from column A in tab ListOfOrders [A7].

And so on.

Is there a formula that I can use to determine what was it to be displayed in column P 7 rows up? How to create such reference?

If anyone has an idea please share as I have spent a lot of time trying different formulas from the list and I still cannot figure it out.

Appreciate any help.

Thank you in advance.

Simon

[samba_ravi]

P2

Please Login or Register  to view this content.

Q2

Please Login or Register  to view this content.

Try this and copy towards down

[samba_ravi]

or
P2=LOOKUP(2,1/(ListOfOrders!$A$5:INDEX(ListOfOrders!$A$6:$A$229,CEILING(ROWS($P$2:$P2)/7,1))<>""),ListOfOrders!$A$5:INDEX(ListOfOrders!$A$6:$A$229,CEILING(ROWS($P$2:$P2)/7,1)))
Q2=INDEX(ListOfOrders!$B$6:$B$229,CEILING(ROWS($P$2:$P2)/7,1))
Try this and copy towards down

[Ramzes]

Hello nflsales,

thank you for the answers.

Both ways work perfectly bringing up exactly what I need.

Thank you once again.