I have a spreadsheet that searches through a sheet filled with data and identify transactions for:
1. A given machine ID
2. Then, it takes that list of transactions and gives me the one that falls on the previous business day
3. Then, it takes the sum of debits and credits to check if they are equal (in balance)
Here is the formula for the debit amount in cell C6. The credits would be the same but point to a different range of cells:
=IFERROR(IF($A6="","",IF(AND($D$2<>"",$D$3<>""),SUMPRODUCT(--(Data!$D$4:$D$25=$A6),--(Data!$C$4:$C$25>=$D$2),--(Data!$C$4:$C$25<=$D$3),Data!$E$4:$E$25),SUMPRODUCT(--(Data!$D$4:$D$25=$A6),--(Data!$C$4:$C$25=$A$3),Data!$E$4:$E$25))),$F$2)
F2 AND F3 POINT TO 2 CELL THAT ALLOWS THE USER TO QUERY A DATE RANGE SO I AM SAYING IF THERE IS A VALID START AND END DATE IN THESE CELLS, USE THESE DATES FOR QUERYING THE DEBIT AND CREDIT AMOUNTS.
CELL A2 IS A CELL THAT CONTAINS THE PREVIOUS BUSINESS DAY.
Is there a way to create a macro that says if the above BOLD portion of the formula is true, then place an “X” in the cell next to each individual transaction that makes up the “In Balance”?
Here is a link to an example file . On the summary page, terminal ABC111 is "in balance", so on the data page the macro would mark with an “X” in column E the 5 debit transactions that make up the “in balance”. Same with the credit side based on the formula in cell E6 on the summary page.
Can this be done? I am fairly new to macros but am teaching myself. Unfortunately, this is beyond my knowledge of macros right now.
Please let me know if you need more information.
Thanks in Advance!!!
1. A given machine ID
2. Then, it takes that list of transactions and gives me the one that falls on the previous business day
3. Then, it takes the sum of debits and credits to check if they are equal (in balance)
Here is the formula for the debit amount in cell C6. The credits would be the same but point to a different range of cells:
=IFERROR(IF($A6="","",IF(AND($D$2<>"",$D$3<>""),SUMPRODUCT(--(Data!$D$4:$D$25=$A6),--(Data!$C$4:$C$25>=$D$2),--(Data!$C$4:$C$25<=$D$3),Data!$E$4:$E$25),SUMPRODUCT(--(Data!$D$4:$D$25=$A6),--(Data!$C$4:$C$25=$A$3),Data!$E$4:$E$25))),$F$2)
F2 AND F3 POINT TO 2 CELL THAT ALLOWS THE USER TO QUERY A DATE RANGE SO I AM SAYING IF THERE IS A VALID START AND END DATE IN THESE CELLS, USE THESE DATES FOR QUERYING THE DEBIT AND CREDIT AMOUNTS.
CELL A2 IS A CELL THAT CONTAINS THE PREVIOUS BUSINESS DAY.
Is there a way to create a macro that says if the above BOLD portion of the formula is true, then place an “X” in the cell next to each individual transaction that makes up the “In Balance”?
Here is a link to an example file . On the summary page, terminal ABC111 is "in balance", so on the data page the macro would mark with an “X” in column E the 5 debit transactions that make up the “in balance”. Same with the credit side based on the formula in cell E6 on the summary page.
Can this be done? I am fairly new to macros but am teaching myself. Unfortunately, this is beyond my knowledge of macros right now.
Please let me know if you need more information.
Thanks in Advance!!!
