Hi all,
Apologies if this is a simple one to answer - my formulas are all breaking down to the point I'm wondering if I'm approaching it the right way.
I have a (manually updated) file tracking shipments and need to identify if these are on track by comparing against an automatically generated excel file I export each week.
I want to be able to return the value of 'shipment status' after checking for the purchase order across 3 different worksheets.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"][/TD]
[TD="width: 98"][/TD]
[/TR]
[TR]
[TD="width: 98"][/TD]
[TD="width: 98"]Book1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Sheet3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] Shipment Status
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Shipment Status[/TD]
[TD][/TD]
[TD]Purchase Order[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Shipment Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]EXW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]waiting info[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]waiting info[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]DDP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]confirmed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]confirmed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Book2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Shipment Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]10[/TD]
[/TR]
[TR]
[TD="width: 98"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Return 'COB'[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've been trying with multiple vlookups with an indirect function built in. I'm sure I only need an extended Index Match, as the PO number is the only criterion, but appear to be having issues with correlating the status value from the 3 different sheets.
The MrExcel guides work fine if I have the formula in the same workbook but as soon as I try to move it the links appear to break
For Book2, Sheet4, Cell=D2
=VLOOKUP(C2,INDIRECT("'"&INDEX(A2:A4,MATCH(TRUE,COUNTIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,0)
-in the above I had my 3 sheets saved as a 'Sheet' list in cells A2:A4 on Sheet4 in Book1, and the formula worked fine
=VLOOKUP(C2,INDIRECT("'"&INDEX(Book1!Sheet,MATCH(TRUE,COUNTIF(INDIRECT("'"&Book1!Sheet&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,FALSE)
-the above is where I have tried to link to the original book, but only appears to return #REF errors. Have I just accidentally missed something?
I've tried to edit a Vlookup/Indirect but with no success
=VLOOKUP(C2,INDIRECT("'"&LOOKUP(REPT("z",255), IF(COUNTIF(INDIRECT("'"&Sheet&"'!A2:A4"),C2),Sheet))&"'!A2:B5"),2,FALSE)
I've also tried a multiple Index/match combo which worked for some of the values but not others - I think only reading parts of the first sheet (when I can manually find many more), and returns mainly #N/A errors
=INDEX('[Book1.xlsm]Sheet1'!$H$2:$H$5&'[Book1.xlsm]Sheet2'!$H$2:$H$5&'[Book1.xlsm]Sheet3'!$H$2:$H$5,MATCH($C2,'[Book1.xlsm]Sheet1'!$A$2:$A$5&'[Book1.xlsm]Sheet2'!$A$2:$A$5&''[Book1.xlsm]Sheet3'!$A$2:$A$5,0))
Not very keen to code VBA but if there's a simpler solution would be open to hearing it as the manual checking and updating of this file takes forever so any streamlining solutions would be appreciated.
Many thanks in advance.
Apologies if this is a simple one to answer - my formulas are all breaking down to the point I'm wondering if I'm approaching it the right way.
I have a (manually updated) file tracking shipments and need to identify if these are on track by comparing against an automatically generated excel file I export each week.
I want to be able to return the value of 'shipment status' after checking for the purchase order across 3 different worksheets.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"][/TD]
[TD="width: 98"][/TD]
[/TR]
[TR]
[TD="width: 98"][/TD]
[TD="width: 98"]Book1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Sheet3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] Shipment Status
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Shipment Status[/TD]
[TD][/TD]
[TD]Purchase Order[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Shipment Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]EXW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]waiting info[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]COB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]waiting info[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]DDP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]confirmed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]confirmed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]booked[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Book2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Purchase Order[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Shipment Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]10[/TD]
[/TR]
[TR]
[TD="width: 98"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Return 'COB'[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've been trying with multiple vlookups with an indirect function built in. I'm sure I only need an extended Index Match, as the PO number is the only criterion, but appear to be having issues with correlating the status value from the 3 different sheets.
The MrExcel guides work fine if I have the formula in the same workbook but as soon as I try to move it the links appear to break
For Book2, Sheet4, Cell=D2
=VLOOKUP(C2,INDIRECT("'"&INDEX(A2:A4,MATCH(TRUE,COUNTIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,0)
-in the above I had my 3 sheets saved as a 'Sheet' list in cells A2:A4 on Sheet4 in Book1, and the formula worked fine
=VLOOKUP(C2,INDIRECT("'"&INDEX(Book1!Sheet,MATCH(TRUE,COUNTIF(INDIRECT("'"&Book1!Sheet&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,FALSE)
-the above is where I have tried to link to the original book, but only appears to return #REF errors. Have I just accidentally missed something?
I've tried to edit a Vlookup/Indirect but with no success
=VLOOKUP(C2,INDIRECT("'"&LOOKUP(REPT("z",255), IF(COUNTIF(INDIRECT("'"&Sheet&"'!A2:A4"),C2),Sheet))&"'!A2:B5"),2,FALSE)
I've also tried a multiple Index/match combo which worked for some of the values but not others - I think only reading parts of the first sheet (when I can manually find many more), and returns mainly #N/A errors
=INDEX('[Book1.xlsm]Sheet1'!$H$2:$H$5&'[Book1.xlsm]Sheet2'!$H$2:$H$5&'[Book1.xlsm]Sheet3'!$H$2:$H$5,MATCH($C2,'[Book1.xlsm]Sheet1'!$A$2:$A$5&'[Book1.xlsm]Sheet2'!$A$2:$A$5&''[Book1.xlsm]Sheet3'!$A$2:$A$5,0))
Not very keen to code VBA but if there's a simpler solution would be open to hearing it as the manual checking and updating of this file takes forever so any streamlining solutions would be appreciated.
Many thanks in advance.
