Hello everybody:
I have two big spreadsheets. Please see a sample attached.
I want to use some formula to insert the po number on sheet 2 to the column C of sheet 1, according to the same invoice numbers on both sheets.
Thank you for your help!
Hello everybody:
I have two big spreadsheets. Please see a sample attached.
I want to use some formula to insert the po number on sheet 2 to the column C of sheet 1, according to the same invoice numbers on both sheets.
Thank you for your help!
Hi Jane,
Any chance you can alter sheet2 so that 'invoice' is before 'po'? If they were switched, you could use the following to get the PO number:
=IF(ISERROR(VLOOKUP(A3,Sheet2!$A$2:$D$14,2,FALSE)),"",VLOOKUP(A3,Sheet2!$A$ 2:$D$14,2,FALSE))
otherwise, you'll have to use:
=IF(ISERROR(MATCH(A3,Sheet2!$B$2:$B$14,0)),"",INDEX(Sheet2!$A$2:$A$14,MATCH (A3,Sheet2!$B$2:$B$14,0)))
Matt
or try
=SUMPRODUCT(--((Sheet2!B$2:B$14)=A3),--(Sheet2!A$2:A$14))
2 questions
How big are your spreadsheets as this could influence the method?
Do you need to retain the formula or just need the data?
Regards
MD
Last edited by mdmackillop; 11-29-2005 at 11:51 AM. Reason: Queries added.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I tried the sample spreadsheet and it works. However, the big spreadsheet doesn't work. I don't know and will try torrow again. The spread sheet has abount 2000 lines
Thank you very much!
Hi Jane,
I had some problems with your cell formats. It might be worth checking these if you have trouble.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote