How to return the starting balance of a bank account in a master list of bank accounts

Lyryx

New Member
Joined
Aug 18, 2015
Messages
45
Hi everyone,


I've built a workbook that evaluates a client's banking information and processes multiple bank accounts at a given time.

I'd like to find a formula that will return the starting balance of each unique bank account from my dataset to reduce human error with data input.

The working paper houses all bank info on a master tab that looks similar to the table at the bottom of this post. Information from this table is then pulled to an Overview tab that updates as more bank info is added to the workbook.

Currently, I need to manually enter the opening balance of the accounts on an overview tab of my workbook to calculate the running total being displayed in the master tab, I'd like to find a formula that will always return the first balance in the "Running Total" column of the table below for each unique Account# to prevent this. Originally, I planned to use a SUMIFS formula based on the account# and a description "Opening Balance" however, this value isn't always included in bank statements or changes to "Balance Forward" or some other variant. I was thinking the Lookup formula could find the first unique Account# Value and then look over 5 columns and return that value (which would be the opening account balance) but I couldn't get it to work...


I appreciate the help with this if anyone has a solutions. Let me know if you need any further clairfications.


Thanks in advance,



The master banking table looks like this.

BankAccount#Transaction DateDescriptionWithdrawalDepositsRunning Total
ABCABC-1232020-01-01Opening Balance- 50.00
ABCABC-1232020-02-10Cash withdrawal25 25.00
CBACBA-3212020-01-01Balance Forward- 75.00
CBACBA-3212020-02-10Cash Deposit25100.00
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Lyryx,

Do you just need the first which appears or do you need the earliest date (i.e. the data may not be in date sequence)?

Assuming your Overview sheet already has the account then an INDEX & MATCH would work.

Dataset
Book1
ABCDEFG
1BankAccount#Transaction DateDescriptionWithdrawalDepositsRunning Total
2ABCABC-1231/1/2020Opening Balance-50
3ABCABC-1232/10/2020Cash withdrawal2525
4CBACBA-3211/1/2020Balance Forward-75
5CBACBA-3212/10/2020Cash Deposit25100
Dataset


Overview with formula
Book1
AB
1Account#First Entry
2ABC-12350
3CBA-32175
Overview
Cell Formulas
RangeFormula
B2:B3B2=INDEX(Dataset!G:G,MATCH(A2,Dataset!B:B,0))
 
Upvote 0
I can't believe I didn't consider index match :ROFLMAO:

Thanks so much!
The data is usually sorted by oldest to newest date so the INDEX Match gets the job done beautifully.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top