Help help taking the data for one date and lookup the data from another date and substracting based on additional characteristics.

falcons07076

New Member
Joined
Nov 11, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello I have a file that has two tabs. In the first tab it lists all bank accounts down the bottom of the page in rows. The list includes details about each account from columns A-H and then it has dates going all the way out from I-EY. The details in A-H have the unique identifier I created in column A and the other details in B-H most relevant for this is the account type(ZBA or Balance) in column G. For the data in I-EY, I have the dates listed across the top in row 2 for all dates going out for each bank account. For each date I populated the balance its a balance account or the activity if it's a ZBA. This is the data that I will use for the other subsequent tab #2. The data in tab 1 is all hardcoded.

In Tab#2, its setup the exact same way. All the bank accounts are in the same rows as tab 1 but the columns are off by one. The dates start one day earlier in tab 1.(tab1 in column I starts with 6/30 and in tab 2 column I starts as 7/1) I will get into why. In tab 2 I want to calculate or pull the daily activity for each account based on the type of account it is. If says balance in column G) i want to calculate for each date the change in balances from the prior day. (So for 7/1 if its a balance account take the balance in 7/1 column J and substract the balance in column I). This will populate the activity for 7/1 in tab 2. If its a ZBA then just take that specific number.

So I was hoping to perform this function I can have a standard if formula but I wanted to avoid any problems on a daily basis by creating a formula in tab 2 that looks up that date(row 2) and what type of account it is. If it's a balance account i want it to take the date in row 2 for each column and have it lookup the balance for the previous day(Date - 1) so it calculates and then if its a ZBA just look up the date and find the balance for that day.

Hope this makes sense. Appreciate all the help

So basically i need a formula that
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

falcons07076

New Member
Joined
Nov 11, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Tab 1
 

Attachments

  • Tab 1.JPG
    Tab 1.JPG
    22.3 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,225
Members
412,372
Latest member
JON_ROCKS
Top