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
44
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
606
Office Version
2016
Platform
Windows
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))
 

Lyryx

New Member
Joined
Aug 18, 2015
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,475
Messages
5,444,696
Members
405,297
Latest member
PaigeWarner

This Week's Hot Topics

Top