Vlookup or MACRO?


New Member
Nov 2, 2005
Could someone help me with this idea i was hoping excel could manage this.

For my company i can export from my database into excel a list of all cheques printed in a date range. i would then like to use this list to "cross off" the cheques on a daily basis when i am doing the bank reconciliation so that on a daily basis i could see the total outstanding cheques.

i have created an excel sheet like this:

Column A lists the Check numbers
Column B lists the Date of the check
Column C lists the Check amount
Bottom of Column C is a grand total amount (i.e checks outstanding)

once i have this long list, could be anywhere from 10 to 500 cheques(rows), on a daily basis i would like to type in, lets say column d row 1, the first check number (which has cleared the bank) and then excel would locate that check from column A and "delete" it so that the amount in column c would not show in the grand total at the end of column C. then i would continue overwritting in column d row 1 typing in the second check number to clear the bank. therefore once i have done all the checks for the day, the grand total at the bottom of column C would show the remaining outstanding balance of all the checks.

This sheet would also have to be able to expand on a weekly basis, as we print more checks i would add them to the bottom of rows form the previouse checks and continue on.

I concider myself a medium user of excel and can understand complex uses of excel if someone told me a "pro" formula, even if i cant im sure my IT guys at work could.

I figure to accomplish this task id probbaly need a macro? but not sure if i could even use that in this case. I thought of using Vlookup but i would have to almost have a conditional formating on the vlookup result which would remain even after the vlookup value is changed... which is where i got lost if this is possible. i could also use access if you think that it is only possible in access?

If i lost you guys somewhere, i would be happy to try and explain with illustrations.... but hopping i was clear enough?

please help :) thanks!

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.


Active Member
Jul 13, 2005
If it is possible for you to add another column after your cleared checks then the example below should help.
1Check No.Check DateCheck AmountCleared ChecksCleared Check Amount
21234501/01/2005$ 10.001235170
31234602/01/2005$ 20.00
41234703/01/2005$ 30.00
51234804/01/2005$ 40.00
61234905/01/2005$ 50.00
71235006/01/2005$ 60.00
81235107/01/2005$ 70.00
91235208/01/2005$ 80.00
101235309/01/2005$ 90.00
111235410/01/2005$ 100.00
121235511/01/2005$ 110.00
131235612/01/2005$ 120.00
141235713/01/2005$ 130.00
151235814/01/2005$ 140.00
161235915/01/2005$ 150.00
171236016/01/2005$ 160.00
181236117/01/2005$ 170.00
191236218/01/2005$ 180.00
201236319/01/2005$ 190.00
21Total$ 1,830.00

The extra column contains a vlookup formula to find the amount of the cleared check from the list of checks, which the total adds together and subtracts it from the total. I've also added a conditional format to the amounts of the checks with the formula "=VLOOKUP(A2,$D:$D,1,FALSE)" which then puts a line throught the check amount.

If you cannot add another column I'm sure someone on this site will be able to help you code a macro as I'm not very good myself.

I hope this helps you out. :)


It appears that things that have strikethrough don't show up using the html maker, but on my spreadsheet cell D8 is struck through.
Upvote 0


New Member
Nov 2, 2005
not tottaly what i wanted

This was kinda what i wanted but not exactly.

you see how in your formulas all you do is make a list in column D, i.e. check 1 is D2, check 2 is D3, check n is Dn.... what i was hoping is that i could just type each check one by one into the d2 cell and as i type the checks in they are removed from the A:C table. i dont want to have a huge list in the D column, as i would also need to know sometimes the exact chest left and dont want to filter them between the A:C strikeouts as you suggested.

if this is impossible then i could use your suggestion. it would work. But in that case i have a second question.

Question #2:
If i have a list of checks in column A with the date in column B and amount in column C, and then i want to merge or remove the matching items from a list of just check numbers can this be done. like on a new sheet, every time there is a match between the column A check numbers and column D it is not reported ont he new list, if there is no match, or i.e. the check has not cleared then it is reported on the list? this could also solve my problem.

but i still would like it to be all done on 1 nice sheet, just entering the cleared checks in $D$2 always, and then the matching check fromt he column A is removed formt he A:C table?

im pretty sure this is not possible? never seen anything like this remotly possible. if it is i would truely believe the power of excel. But thanks for your suggestiong it is also a viable solution.

Thanks for the Help guys.... hope you can possible solve the way i have posted it?

Upvote 0


MrExcel MVP
May 28, 2004
Using Ste_Moore01's post as a guide, you could use a Worksheet_Change() event to do this. Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    If Target.Count <> 1 Then Exit Sub          'more than 1 cell?  exit
    If Target.Row <> 2 Then Exit Sub            'not Row 2?  exit
    If Target.Column <> 4 Then Exit Sub         'not Column D?  exit
    Range("A:A").AutoFilter 1, Target.Value     'filter Column A for entered check no.
    Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Delete xlUp  'delete those trans.
    ActiveSheet.AutoFilterMode = False          'disable filters

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

That code would go in the particular worksheet's code module.

  • How to use the above code:

    Press Alt-F11 to open the VBE.
    Press Control-R to open the Project Explorer.
    Click "Microsoft Excel Objects" for the file you're working on.
    Double-click the sheet module where you need this to work.

    Open the Code pane with F7.
    Paste the above code in.
    Press Alt-Q to close the VBE and return to Excel

Hope that helps! Post back if you have any questions.
Upvote 0

Forum statistics

Latest member

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