VBA Easy lookup help

ryansapp

Board Regular
Joined
Apr 22, 2013
Messages
54
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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

ryansapp

Board Regular
Joined
Apr 22, 2013
Messages
54
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top