VBA Script To Calculate the Balance

S

shah0101

Board Regular
Joined
Jul 4, 2019
Messages
118
Dear Experts,

I have a workbook with two sheet with two different tables. (as below images).

Table 1 has Invoice Numbers, Invoice Values and LC Numbers (Table Name is "Inv_21")
Table 2 has the LC Numbers and LC Values (Table Name is "LC_21"


Can I request for a script / module to bring up all the Invoice Numbers and Invoice Values from Table "Inv_21" with an automatic populated Drop Down List of any particular LC Number from Table: "LC_21" which matches the LC Number in both Tables. Then total invoice values of collected invoices and deduct those values from the LC Value?

I have deleted / sanitized as much data as I can on the workbook which I can also share, if needs be.

Much Appreciated.

Thanks.




Table: "Inv_21":
1643362503533.png





Table: "LC_21"
1643362525236.png






Result Sheet:
1643363171191.png
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
1st table in sheet1, 2nd table in Sheet2. Result sheet is Sheet3
In Sheet3
For LC list
In G2 then copy down till blank cell is seen
Excel Formula: 
=IFERROR(INDEX(Inv_21[LC NO.],AGGREGATE(15,6,ROW(Inv_21[LC NO.])/((Inv_21[LC NO.]<>"")*(ISERROR(MATCH(Inv_21[LC NO.],Sheet3!$G$1:$G1,0)))*(ISNUMBER(MATCH(Inv_21[LC NO.],LC_21[L/C NO.],0)))),1)-ROW($A$1)),"")
A3=LC value
For data validation list in B3:
Select B3 -->DATA-->data validation-->In Allow select List-->In Source enter this formula
=OFFSET($G$2,0,0,COUNTIF($G$2:$G$2000,"?*"))
This will give drop down list with your conditions.
A5=Inv Nos, B5=Value
In A6
Excel Formula: 
=IFERROR(INDEX(Inv_21[INVOICE NO.],AGGREGATE(15,6,ROW(Inv_21[INVOICE NO.])/(INDIRECT("Inv_21[LC NO.]")=Sheet3!$B$3),ROWS($A$6:$A6))-ROW($A$1)),"")

In B6
Excel Formula: 
=IFERROR(INDEX(Inv_21[NET VALUE],AGGREGATE(15,6,ROW(Inv_21[NET VALUE])/(INDIRECT("Inv_21[LC NO.]")=Sheet3!$B$3),ROWS($A$6:$A6))-ROW($A$1)),"")
copy down both till blank cell is seen

In C3=LC Bal Val
In D3
Excel Formula: 
=INDEX(LC_21[AMOUNT],MATCH(Sheet3!$B$3,LC_21[L/C NO.],0))-SUM(Sheet3!$B$6:$B$10)
D3 will give your final result
 
Upvote 0
kvsrinivasamurthy said:
1st table in sheet1, 2nd table in Sheet2. Result sheet is Sheet3
In Sheet3
For LC list
In G2 then copy down till blank cell is seen
Excel Formula: 
=IFERROR(INDEX(Inv_21[LC NO.],AGGREGATE(15,6,ROW(Inv_21[LC NO.])/((Inv_21[LC NO.]<>"")*(ISERROR(MATCH(Inv_21[LC NO.],Sheet3!$G$1:$G1,0)))*(ISNUMBER(MATCH(Inv_21[LC NO.],LC_21[L/C NO.],0)))),1)-ROW($A$1)),"")
A3=LC value
For data validation list in B3:
Select B3 -->DATA-->data validation-->In Allow select List-->In Source enter this formula
=OFFSET($G$2,0,0,COUNTIF($G$2:$G$2000,"?*"))
This will give drop down list with your conditions.
A5=Inv Nos, B5=Value
In A6
Excel Formula: 
=IFERROR(INDEX(Inv_21[INVOICE NO.],AGGREGATE(15,6,ROW(Inv_21[INVOICE NO.])/(INDIRECT("Inv_21[LC NO.]")=Sheet3!$B$3),ROWS($A$6:$A6))-ROW($A$1)),"")

In B6
Excel Formula: 
=IFERROR(INDEX(Inv_21[NET VALUE],AGGREGATE(15,6,ROW(Inv_21[NET VALUE])/(INDIRECT("Inv_21[LC NO.]")=Sheet3!$B$3),ROWS($A$6:$A6))-ROW($A$1)),"")
copy down both till blank cell is seen

In C3=LC Bal Val
In D3
Excel Formula: 
=INDEX(LC_21[AMOUNT],MATCH(Sheet3!$B$3,LC_21[L/C NO.],0))-SUM(Sheet3!$B$6:$B$10)
D3 will give your final result
Click to expand...
Hi,

Thanks for your time.

On first look it seems that this will bring up the totals which is okay but nothing another part is still missing which is the breakup I mean if Inv_21 has three entries which matches with any corresponding LC_21 then I need these three invoice numbers and their respective values and then the totals in the bottom.

Would this formula bring those details as well?
 
Upvote 0
shah0101 said:
Hi,

Thanks for your time.

On first look it seems that this will bring up the totals which is okay but nothing another part is still missing which is the breakup I mean if Inv_21 has three entries which matches with any corresponding LC_21 then I need these three invoice numbers and their respective values and then the totals in the bottom.

Would this formula bring those details as well?
Click to expand...


For ease I have uploaded a sample file which can be downloaded from below link:

we.tl

LC-Balances-21-001.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world
we.tl we.tl
 
Upvote 0
In Sheet1
In G2 copy down
Excel Formula: 
=IFERROR(INDEX(Inv_21[LC NO.],AGGREGATE(15,6,ROW(Inv_21[LC NO.])/((Inv_21[LC NO.]<>"")*(ISERROR(MATCH(Inv_21[LC NO.],Sheet1!$G$1:$G1,0)))*(ISNUMBER(MATCH(Inv_21[LC NO.],LC_21[L/C NO.],0)))),1)-ROW($A$1)),"")
For DV in D11
=OFFSET($G$2,0,0,COUNTIF($G$2:$G$2000,"?*"))
In C15
=IFERROR(INDEX(Inv_21[INVOICE NO.],AGGREGATE(15,6,ROW(Inv_21[NET VALUE])/(INDIRECT("Inv_21[LC NO.]")=Sheet1!$D$11),ROWS($A$6:$A6))-ROW($A$1)),"")
In D15
=IFERROR(INDEX(Inv_21[NET VALUE],AGGREGATE(15,6,ROW(Inv_21[NET VALUE])/(INDIRECT("Inv_21[LC NO.]")=Sheet1!$D$11),ROWS($A$6:$A6))-ROW($A$1)),"")
Copy down both.
In F11
=INDEX(LC_21[AMOUNT],MATCH(Sheet1!$D$11,LC_21[L/C NO.],0))-SUM(Sheet1!$D$15:$D$15000)
Result is below. Let Me know the problem
ABCDEFG
711010188995-L
DPCBGE350178
236321060093
101921060082
101921050215
RESULT SHEET:088721060014
147521060704
"LC NUMBER" DROP DOWN WITH AUTO POPLUATED DROP DOWN LIST:0692621IM00000750692621IM0000075

209921060085
LC VALUE:DPCBGE350178LC BALANCE VALUE:$1,510.00236321060128
001LM01211320007
236321060092LC AMOUNT LESS THE INVOICE VALUE236321060092
INVOICES:VALUE:CINITF2100193
EI21-352-A
17953.9​
5677603768
EI21-352-B
9633.8​
5672603850
EI21-352-C
36119.2​
1541FLC210275
5675603768
5676603768
1858IMLC23493
IMLC403012101499
DPCBGE351750
0480421IM0000358
1541FLC210334
5674603768
5678603820
5676603790
5546607966
5542608080
 
Upvote 0
Solution
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted to multiple sites. Please provide links to all sites where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted to multiple sites. Please provide links to all sites where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Noted. Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Find matching value in table, check if specific cell in same row is empty, find next if not
Replies
0
Views
204
dubyarly
D
N
  • Solved
How to open one workbook find a max value in one worksheet and return it from a function
Replies
2
Views
170
Nicole H
N
S
  • Question
VBA Combo Box populating based on entry in another Combo Box and filtered by another column
Replies
1
Views
92
skciws
S
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
192
myall_blues
myall_blues
W
  • Question
Matching two reports
Replies
1
Views
173
Joe4
Joe4

Forum statistics

Threads
1,214,968
Messages
6,122,506
Members
449,089
Latest member
RandomExceller01

Share this page

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