data import from one sheet to other

[aarti_rto]

Hello,

i have a data like below in Sheet 1 for 12 months ..

I need " New PO" Colum from sheet 1 to Sheet 2 WRT month like below screenshot.
But it should be dynamic. means whenever i change / modify the "new po" column in sheet 1 , it should automatically update in sheet 2.

 

[kvsrinivasamurthy]

In Sheet2 in D5, copied down and across as required. Assumed the B:C are identical in both the sheets (order is not changed).

=INDEX(Sheet1!$E$9:$O$18,ROWS(Sheet1!D$5:D5),MATCH(D$3,Sheet1!$E$6:$O$6,0)+MATCH(D$4,INDEX(Sheet1!$E$8:$O$8,MATCH(D$3,Sheet1!$E$6:$O$6,0)):Sheet1!$O$8,0)-1)

 

[aarti_rto]

thank you..

it's working if sequence is same..

but how to derive the same data if there is no sequence..

actually in reality i have 1500 nos rows in Sheet 1 & it's not necessary that for all 1500 nos "New PO " column is filled.

i only feed the "New PO" Column only if required...

is it possible that in Sheet 2 , only those rows will add/ display with NEW PO columns where the value is greater than zero in Sheet 1?? ( i need filtered data monthwise for each part's NEW PO qty , in Sheet 2)

 

[kvsrinivasamurthy]

Sequence need not be same. But "New Po" should be within the month. Each month must have the "New P"' column.
Eg: For Nov-23 "New Po" should be in K8:O8.
To avoid 0 display try

=IFERROR(1/(1/(INDEX(Sheet1!$E$9:$O$18,ROWS(Sheet1!D$5:D5),MATCH(D$3,Sheet1!$E$6:$O$6,0)+MATCH(D$4,INDEX(Sheet1!$E$8:$O$8,MATCH(D$3,Sheet1!$E$6:$O$6,0)):Sheet1!$O$8,0)-1))),"")

 

[aarti_rto]

Sequence need not be same. But "New Po" should be within the month. Each month must have the "New P"' column.
Eg: For Nov-23 "New Po" should be in K8:O8.
To avoid 0 display try

=IFERROR(1/(1/(INDEX(Sheet1!$E$9:$O$18,ROWS(Sheet1!D$5:D5),MATCH(D$3,Sheet1!$E$6:$O$6,0)+MATCH(D$4,INDEX(Sheet1!$E$8:$O$8,MATCH(D$3,Sheet1!$E$6:$O$6,0)):Sheet1!$O$8,0)-1))),"")

part no i.e. data in column B matching is also required..

 

[kvsrinivasamurthy]

This formula works even if "New Po" Is missing in some months.
In D5 and copied to full range.

=IFERROR(1/(1/(INDEX(Sheet1!$E$9:$O$18,ROWS(Sheet1!D$5:D5),MATCH(D$3,Sheet1!$E$6:$O$6,0)+MATCH(D$4,INDEX(Sheet1!$E$8:$O$8,MATCH(D$3,Sheet1!$E$6:$O$6,0)):INDEX(Sheet1!$E$8:$O$8,IFERROR(MATCH(EOMONTH(D$3,0)+1,Sheet1!$E$6:$O$6,0)-1,COLUMN(Sheet1!$O$8)-COLUMN($E$6))))))),"")

 

[kvsrinivasamurthy]

PL upload the sample file in some site and give link here.

 

[kvsrinivasamurthy]

Try. In D5 copied to full range.

=IFERROR(1/(1/(INDEX(Sheet1!$E$9:$O$18, MATCH($B5,Sheet1!$B$9:$B$18,0),MATCH(D$3,Sheet1!$E$6:$O$6,0)+MATCH(D$4,INDEX(Sheet1!$E$8:$O$8,MATCH(D$3,Sheet1!$E$6:$O$6,0)):INDEX(Sheet1!$E$8:$O$8,IFERROR(MATCH(EOMONTH(D$3,0)+1,Sheet1!$E$6:$O$6,0)-1,COLUMN(Sheet1!$O$8)-COLUMN($D$6))))-1))),"")