none
Compare two worksheets, for values, based on date range RRS feed

  • Question

  • Good evening all.

    I have a workbook which I've put together, for the purpose of tracking my costs, expenses, income, etc.......

    On one worksheet, I've imported all of my official banking activity, and update it regularly. Account numbers in column A, activity dates- Column B, dep's in Column C, debits in Column D, etc......

    On another worksheet I have what's essentially a check register. 

    I want to compare debits, deposits, etc..... between the two sheets. I basically want to make sure that all of my transactions are correctly input, and if something is missing, I want to set some kind of an alert, so I can track them down, and keep up on things.

    As it appears that most, if not all of my transactions occur at the bank within a few days after I do them in real life, I was thinking that it'd be a good thing to use dates as the limiter. Perhaps using a range of 3-7 days on either side of the date on my receipts-- which I input on my register worksheet.

    How would I accomplish this?

    TYIA


    • Edited by SteveDB1 Friday, February 5, 2016 5:24 AM forgot a couple elements
    Friday, February 5, 2016 5:21 AM

Answers

All replies

  • Hi SteveDB1

    If I got it right you need to reconcile your bank statement(s) against your books
    Starting point is to use some not complex formulas in combination with conditional formatting
    In addition I would use tables in both worksheets for easy referencing.
    ( No VBA is required ) 

    Let me know if you need support and we build solution.

    Best regards and nice weekend

    GioPir
    Friday, February 5, 2016 5:56 PM
  • Hi Gio.

    Thank you for your response.

    Yes, please. 

    I'm basically familiar with the name manager, and conditional formatting is no problem. As for tables, my work hasn't gotten me involved in that. 

    Again, thank you for your time, and response.

    Best.

    Friday, February 5, 2016 11:00 PM
  • Hi SteveDB1

    Ok, I had some free time to make simple template. By the way you gave me an idea to follow up my own expenses, otherwise to run down into minus is easy. One day when we get reach personal accountant can handle income-spending.  J

    I created two worksheets “Bank statement” and “My Books”

    Respectively “Table1” is to copy “Bank statement” and “Table2” to register or copy list of transactions during a period.

    • Transactions which are not listed in one of the sheets are marked in light red color.
    • If amounts mismatch, such transactions are marked also in light red
    • If “Value date” exceeds max 7 days such lines are also marked. 

    In addition, you have “Notification Section” showing status. Cells marked in dark red

    Refer to snapshot below:




    Is this what you are looking for?


    Regards
    Saturday, February 6, 2016 9:36 PM
  • Hi Gio.

    From what I can see.... yes. This appears to be comparable to what I'm looking for.

    Saturday, February 6, 2016 11:49 PM
  • Hi Steve

    Download file form this link
    https://drive.google.com/file/d/0B4mitJ-SIt9bT05kYWRiX2Q1MzA/view?usp=sharing

    File is simple and I'm sure You will easily modify the way it meets your requirements.If you find it helpful, please mark as answered to move on.  

    Best regards and good luck with project

    Gio
    • Proposed as answer by GioPir Sunday, February 7, 2016 10:12 AM
    • Marked as answer by SteveDB1 Monday, February 8, 2016 12:53 AM
    Sunday, February 7, 2016 10:12 AM
  • Hi Gio.

    Thank you very much.

    I need to adjust it for my purposes, and do some reading on tables--- I made something different when I did what I thought was table work. I did however use the sumproduct-- just not like this. It became my favorite tool. Have a great week.

    Best.

    Monday, February 8, 2016 12:53 AM