Dausbrooks
New Member
- Joined
- Feb 5, 2020
- Messages
- 1
- Office Version
- 2010
- Platform
- Windows
I have two separate spreadsheets for my business. My sales spreadsheet lists all services by date with the revenue charged including associated taxes and tips. I get a separate spreadsheet that lists all credit card transactions which contains the date of transaction and total revenue but also the associated credit card fees. My issue is the date of transaction could differ from the date of service. It could lag up to three days.
My simplified Credit Card Charges spreadsheet:
My simplified Sales spreadsheet with Column H and I added.
I was considering using an Index & Match to pull my credit card fees to the sales spreadsheet.
Assumptions:
I have multiple sales on a given day. (up to 20) therefore I rarely have the matching revenue charges within a three day period.
The credit card charges typically fall within 3 days of the actual date of service.
Dates in spreadsheet are numerical.
My goal is to have a running list of deposits for any given day that is accurate within the assumptions.
My thought was to index by using an inserted column which combines columns one and two of each spreadsheet i.e. Sales!A2&B2 with Credit_transactions!A2&B2 However I am unsure how I can look for a range using the Index and Match functions. Perhaps there is a much simpler means that I am unaware?
My simplified Credit Card Charges spreadsheet:
My simplified Sales spreadsheet with Column H and I added.
I was considering using an Index & Match to pull my credit card fees to the sales spreadsheet.
Assumptions:
I have multiple sales on a given day. (up to 20) therefore I rarely have the matching revenue charges within a three day period.
The credit card charges typically fall within 3 days of the actual date of service.
Dates in spreadsheet are numerical.
My goal is to have a running list of deposits for any given day that is accurate within the assumptions.
My thought was to index by using an inserted column which combines columns one and two of each spreadsheet i.e. Sales!A2&B2 with Credit_transactions!A2&B2 However I am unsure how I can look for a range using the Index and Match functions. Perhaps there is a much simpler means that I am unaware?