Hi guys
here my project is extemely complicated to calculate the stock across sheets by using userform
what I want :
1- when run the userform should be optionbutton(stock) default
and calculation the stock ( before calculation if there is duplicate item for each sheet alone should be merge for each duplicate item for each sheet alone based on column D except sheet FFR doesn't contains duplicate items at all ) and the formula based on sheets names for columns H,J should be( PURCHASE-SELLING +RETURNSS-RETURPP+FFR) based on column ID-CC
based on optionbutton (STOCK) take IDTR-100 as example
when merge in sheet PURCHASE= column (QTY)=90 & column (TOTAL) =10,200 as to sheet SELLING = column (QTY)=15 & column (TOTAL) =1,770 as to sheet RETURNSS column (QTY)=2 & column (TOTAL) =220 as to sheet RETURNPP column (QTY)=5 & column (TOTAL) =600 as to sheet FFR column (QTY)=200 & column (TOTAL) =24000
now the final result QTY=90-15+2-5+200=272(as show in listbox based on selected STOCK)
TOTAL=10,200-1,770+220-600+24,000=32,050(as show in listbox based on selected STOCK )
with considering there is minus values becuase there are some items are existed in some sheets but not all when implement the formula for calculation will give you minus
as to optionbutton PURCHASE
it includes three sheets when calculation (PURCHASE -RETURNPP+FFR)
take IDTR-102 as example
when merge in sheet PURCHASE= column (QTY)=5 & column (TOTAL) =500 as to sheet RETURNPP column (QTY)=4 & column (TOTAL) =400 as to sheet FFR column (QTY)=20 & column (TOTAL) =2000
the final result QTY=5-4+20=21(as show in listbox based on selected PURCHASE)
TOTAL=500-400+2000=2100 (as show in listbox based on selected PURCHASE)
as to optionbutton (SALES)
it includes two sheets when calculation (SELLING-RETURNSS)
take IDTR-110 as example
when merge in sheet SELLING = column (QTY)=0 & column (TOTAL) =0(there is not existed this item) as to sheet RETURNSS column (QTY)=10 & column (TOTAL) =3000
the final result QTY=0-10=-10(as show in listbox based on selected SALES)
TOTAL=0-3000=-3000 (as show in listbox based on selected SALES )
last thin should also after select one of them optionbutton when write the item based on column contain header ID-CC into textbox1 should filter in list box based on written into textbox1. and ignore columns CLIENT NO , INVOICE NO & PRICE when populate data in listbox .
note : each picture I did manually to understand my idea
if anybody interest to help me please inform me if need more details
thanks
Bookmarks