Hi all,
I have a workbook with multiple worksheets, the workbook is used for project cost monitoring with a new worksheet for each project. The sheets have unique names associated with their project.
Col's A-M are currently used.
Col A contains the Purchase Requisition No.
Col D contains the Purchase Order No.
There is a worksheet (named report) that is linked to the purchasing system via an external workbook. From this I have a number of columns auto filling via Vlookup from the 'report' tab,
eg: =VLOOKUP(B7,report!$A$1:$V$64000,8,FALSE)
(note, B7 is concatenate of A7 and ' , due to an issue with the report I have no control over)
A single PR does not guarantee a single PO:banghead: so I have used conditional formatting to highlight where there are multiple PR entries in the report sheet.
From here I have used a array to list all PO associated with a single PR
eg: '{=IFERROR(INDEX(report!$H$8:$H$64000,SMALL(IF($C$18=report!$A:$A,ROW(report!$H$8:$H$64000)-14,""),ROW()-16)),"Error")}
The above array is copied into 25 cells, and works fine with me just entering the PR number highlighted by the previous conditional formatting.
Here is where I'm stuck
I would like to use the value entered into C18 to search Col A for the same value.
At which point I will add a new row below the existing entry, copy cells down with formulae for all columns & then double back to the PO column to manually enter the one (or more) additional PO no.( the last bit I have completed previously)
I hope that makes sense?
SteveN
I have a workbook with multiple worksheets, the workbook is used for project cost monitoring with a new worksheet for each project. The sheets have unique names associated with their project.
Col's A-M are currently used.
Col A contains the Purchase Requisition No.
Col D contains the Purchase Order No.
There is a worksheet (named report) that is linked to the purchasing system via an external workbook. From this I have a number of columns auto filling via Vlookup from the 'report' tab,
eg: =VLOOKUP(B7,report!$A$1:$V$64000,8,FALSE)
(note, B7 is concatenate of A7 and ' , due to an issue with the report I have no control over)
A single PR does not guarantee a single PO:banghead: so I have used conditional formatting to highlight where there are multiple PR entries in the report sheet.
From here I have used a array to list all PO associated with a single PR
eg: '{=IFERROR(INDEX(report!$H$8:$H$64000,SMALL(IF($C$18=report!$A:$A,ROW(report!$H$8:$H$64000)-14,""),ROW()-16)),"Error")}
The above array is copied into 25 cells, and works fine with me just entering the PR number highlighted by the previous conditional formatting.
Here is where I'm stuck
I would like to use the value entered into C18 to search Col A for the same value.
At which point I will add a new row below the existing entry, copy cells down with formulae for all columns & then double back to the PO column to manually enter the one (or more) additional PO no.( the last bit I have completed previously)
I hope that makes sense?
SteveN