Vlookup or MACRO?

[maggz]

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!

 

[Ste_Moore01]

If it is possible for you to add another column after your cleared checks then the example below should help.

Book1
	A	B	C	D	E
1	Check No.	Check Date	Check Amount	Cleared Checks	Cleared Check Amount
2	12345	01/01/2005	$ 10.00	12351	70
3	12346	02/01/2005	$ 20.00
4	12347	03/01/2005	$ 30.00
5	12348	04/01/2005	$ 40.00
6	12349	05/01/2005	$ 50.00
7	12350	06/01/2005	$ 60.00
8	12351	07/01/2005	$ 70.00
9	12352	08/01/2005	$ 80.00
10	12353	09/01/2005	$ 90.00
11	12354	10/01/2005	$ 100.00
12	12355	11/01/2005	$ 110.00
13	12356	12/01/2005	$ 120.00
14	12357	13/01/2005	$ 130.00
15	12358	14/01/2005	$ 140.00
16	12359	15/01/2005	$ 150.00
17	12360	16/01/2005	$ 160.00
18	12361	17/01/2005	$ 170.00
19	12362	18/01/2005	$ 180.00
20	12363	19/01/2005	$ 190.00
21		Total	$ 1,830.00
Sheet1

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.


**EDIT**

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

 

[maggz]

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?

thanks

 

[Tazguy37]

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.