VBA Easy lookup help

[ryansapp]

Paid Column (M)	Notes Column (N)	Formula Column (O)
Paid		=if(countif(Unpaid!C:C,C4)>0,"Unpaid","Paid") <tbody> </tbody>
Unpaid		=if(countif(Unpaid!C:C,C4)>0,"Unpaid","Paid") <tbody> </tbody>
Paid		=if(countif(Unpaid!C:C,C4)>0,"Unpaid","Paid") <tbody> </tbody>
Paid		=if(countif(Unpaid!C:C,C4)>0,"Unpaid","Paid") <tbody> </tbody>

<tbody>
</tbody>

Briefing: As you'll notice, I've got a file that is maintained through another dept that clarifies in column M whether or not particular invoices have been "Paid" or "Unpaid". In column O we insert a formula that comes directly from another excel files "Paid" tab that is also maintained by that department. They're outsourcing some of this work to me, and I know there must be a more simple way of doing this.

Needs: When I paste into column O, I'd like it to copy the formula all the way down in the scenario where M actually has data (Paid or Unpaid). At this point, I am looking at the scenario where column M has an "Unpaid" and column O has a "Paid", we'll want to change the "Unpaid" cell in column M to "Paid". For each scenario just like that, repeat the process through the cells and then continue with the subsequent sheets.

If there is anything else you need let me know, I'm doing all I can here but I'm not too advanced with writing code just yet. THANKS!

 

[ryansapp]

Maybe its not so easy. Initially I was thinking it would be requiring a loop (which I've never bothered using) and a boolean search matching up all the "Unpaid" and "Paid" cells appropriately, and THEN statement as well overwriting the "Unpaid" cells with "Paid" if they'd need updating.

The formula I'm currently using comes from another workbook that looks like this =IF(COUNTIF('[Sept14 Com Stmt Report Final.xlsx]Unpaid'!C:C,C2)>0,"Unpaid","Paid")

For simplicty, I have pasted the sheet from the other workbook into the original one with all the sheets I'll have to run this macro through but it would be nice/beneficial if it could pull from that other workbook someday.