Calculating formula by condition set in another excel sheet based on common column

J

jatinjoshi

New Member
Joined
Feb 5, 2014
Messages
34
Hi,

I need to solve this thing. I have two excel file Dealbook and Rate, In both the files there is one common field called party
Located in AH Column in dealbook and A Column in Rate.

In Dealbook there is one column called Price/KG located in column J and in Rate Book there are two column % in B and Fix in C. I want to create new column in Dealbook called sale rate by using formula based in Rate Book % (B) and Fix (C) for the party

for e.g. in Dealbook there is party code KKR Which has .34(%) in b and 1.4 in C (FIX) in RATE book and has Price/KG 84 then sale rate will be 84+((84*.34%)+1.4) = 85.68 (in two decimal) and so on

Also in Dealbook I want to create following forumula in new columns

=AH2&"-"&T2&"-"&B2 (S Bill No)
=AI2*P2 (Amount)

and want to sort final book by S Bill No



Here is the Dealbook
SeasonSaleNoAuctionDateSessionTimeLotNoMarkGradeQty (Pkgs)Invoice NoPrice/KgAuct. ValPackages Wt/PkgAuct CodeBasePriceTotal GrossWtNtWtSampleQty (Kgs)Short/ExcessWt (Kgs)Total NtWtTypeDealNoWithInExportInterstatePreferenceDeal TimeSessionTypeDeal Assigned ToWare HouseItem NameAmountBill NoChest WeightPARTYSale RateAmountBill No
2014711/02/2014AllABL0030NamburnadiBP115C513849530ABL145345000450Leaf47758101500111/02/2014 2:44:01 PMNormalSelfJAYSHREEP7TDRABL0030 - Namburnadi - BP1 - 737800ABL - Leaf - 7 - 10.2KKR
2014711/02/2014AllABL0039BHUYANKHATBP11C146612514534ABL1380.6374140360Leaf47758611100111/02/2014 2:49:01 PMNormalSHREE VINAYAK TEA ENTERPRISESBEHERAP1TDRABL0039 - BHUYANKHAT - BP - 746750ABL - Leaf - 7 - 10.6KKR
2014711/02/2014AllATB0011MokrungPF20C3125415019428ATB156856000560Leaf47773472000111/02/2014 4:20:00 PMNormalSelfBSSONAIATB0011 - Mokrung - PF - 784000ATB - Leaf - 7 - 10.4KKR
2014711/02/2014AllATB0012GatoongaBOP10C1168915018424ATB124424000240Leaf47773811000111/02/2014 4:24:01 PMNormalSelfBSSONAIATB0012 - Gatoonga - BOP - 736000ATB - Leaf - 7 - 10.4BGRJ
2014711/02/2014AllATB0038TinkhariaBOPSM15C94812016829ATB14414358.80426.2Leaf47773601500111/02/2014 4:21:01 PMNormalSelfTEWARI/FSHEDATB0038 - Tinkharia - BOPSM - 752200ATB - Leaf - 7 - 10.4BGRJ
2014711/02/2014AllCBL0008BorbamBOP10C130413016525CBL1254250140236Leaf47776801000111/02/2014 4:54:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0008 - Borbam - BOP - 732500CBL - Leaf - 7 - 10.4BGRJ
2014711/02/2014AllCBL0009BorbamBOPSM10C130713116525CBL125425070243Leaf47776781000111/02/2014 4:54:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0009 - Borbam - BOPSM - 732750CBL - Leaf - 7 - 10.4BGRJ
2014711/02/2014AllCBL0011BorbamBOPSM10C130213417025CBL125425000250Leaf47776711000111/02/2014 4:53:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0011 - Borbam - BOPSM - 733500CBL - Leaf - 7 - 10.4BPAT
2014711/02/2014AllCBL0019BorbamBP20C127813118026CBL1528520140506Leaf47777292000111/02/2014 4:57:01 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0019 - Borbam - BP - 768120CBL - Leaf - 7 - 10.4BPAT
2014711/02/2014AllCBL0044SuntokBOP15C58913516028CBL142342000420Leaf47777691500111/02/2014 5:03:01 PMNormalSHREE VINAYAK TEA ENTERPRISESBEHERAP1TDRCBL0044 - Suntok - BOP - 756700CBL - Leaf - 7 - 10.2BPAT
2014711/02/2014AllCBL0051BehaliBOP10C161142P13116530CBL130230070293Leaf47777561000111/02/2014 5:01:00 PMNormalSelfOCTAVIUSCBL0051 - Behali - BOP - 739300CBL - Leaf - 7 - 10.2BPAT
2014711/02/2014AllCBL0058NyagograBP20C871393P13517023CBL146846000460Leaf47778212000111/02/2014 5:07:00 PMNormalSHREE VINAYAK TEA ENTERPRISESOCTAVIUSCBL0058 - Nyagogra - BP - 762100CBL - Leaf - 7 - 10.4KKR
2014711/02/2014AllCBL0059NyagograBP10C871422P13616522CBL122422000220Leaf47778261000111/02/2014 5:07:59 PMNormalSHREE VINAYAK TEA ENTERPRISESKANOITEACBL0059 - Nyagogra - BP - 729920CBL - Leaf - 7 - 10.4BGRJ
2014711/02/2014AllCBL0062PertabghurBP15C241372P13115534CBL151351000510Leaf47778451500111/02/2014 5:08:59 PMNormalSHREE VINAYAK TEA ENTERPRISESITSATDRCBL0062 - Pertabghur - BP - 766810CBL - Leaf - 7 - 10.2GTPP
2014711/02/2014AllCBL0064PertabghurPF6C241375P14014535CBL1211.221000210Leaf4777795600211/02/2014 5:05:00 PMNormalSelfITSATDRCBL0064 - Pertabghur - PF - 729400CBL - Leaf - 7 - 20.2GTPP
2014711/02/2014AllCBL0075HarchurahBOP10C091018P14116531CBL131231070303Leaf47778531000111/02/2014 5:10:00 PMNormalSHREE VINAYAK TEA ENTERPRISESKANOITEACBL0075 - Harchurah - BOP - 743710CBL - Leaf - 7 - 10.2ATCR
2014711/02/2014AllCBL0083AttareekhatPF30C011215P11113040CBL112061200001200Leaf47778753000111/02/2014 5:13:59 PMNormalSelfESSSONAICBL0083 - Attareekhat - PF - 7133200CBL - Leaf - 7 - 10.2ATCR
2014711/02/2014AllCBL0092BUDLABETABOP20C188113215021CBL142842000420Leaf47778492000111/02/2014 5:09:59 PMNormalSHREE VINAYAK TEA ENTERPRISESAPEEJAYCBL0092 - BUDLABETA - BOP - 755440CBL - Leaf - 7 - 10.4KKR
2014711/02/2014AllJTC0031DejooBP23C120813215527JTC1630.262100621Leaf47714022300111/02/2014 9:20:59 AMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONEJTC0031 - Dejoo - BP - 781972JTC - Leaf - 7 - 10.4BPAT
2014711/02/2014AllJTC0035DejooBP112C121312212035JTC1422.442000420Leaf47711871200111/02/2014 9:12:00 AMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONEJTC0035 - Dejoo - BP1 - 751240JTC - Leaf - 7 - 10.2BGRJ

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=4></COLGROUP>


Here is the Rate Book

PartyCode%Fix
KKR0.341.4
ATCR1.340.4
BGRJ0.341.4
CTCA1.340.4
GTPP0.341.05
BPAT2.30.7

<TBODY>
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>

Here is the result Nedded


SeasonSaleNoAuctionDateSessionTimeLotNoMarkGradeQty (Pkgs)Invoice NoPrice/KgAuct. ValPackages Wt/PkgAuct CodeBasePriceTotal GrossWtNtWtSampleQty (Kgs)Short/ExcessWt (Kgs)Total NtWtTypeDealNoWithInExportInterstatePreferenceDeal TimeSessionTypeDeal Assigned ToWare HouseItem NameAmountBill NoChest WeightPARTYSale RateAmountS Bill No
2014711/02/2014AllCBL0075HarchurahBOP10C091018P14116531CBL131231070303Leaf47778531000111/02/2014 5:10:00 PMNormalSHREE VINAYAK TEA ENTERPRISESKANOITEACBL0075 - Harchurah - BOP - 743710CBL - Leaf - 7 - 10.2ATCR141.8843982.61ATCR-Leaf-7
2014711/02/2014AllCBL0083AttareekhatPF30C011215P11113040CBL112061200001200Leaf47778753000111/02/2014 5:13:59 PMNormalSelfESSSONAICBL0083 - Attareekhat - PF - 7133200CBL - Leaf - 7 - 10.2ATCR111.78134132.9ATCR-Leaf-7
2014711/02/2014AllATB0012GatoongaBOP10C1168915018424ATB124424000240Leaf47773811000111/02/2014 4:24:01 PMNormalSelfBSSONAIATB0012 - Gatoonga - BOP - 736000ATB - Leaf - 7 - 10.4BGRJ151.9136458.4BGRJ-Leaf-7
2014711/02/2014AllATB0038TinkhariaBOPSM15C94812016829ATB14414358.80426.2Leaf47773601500111/02/2014 4:21:01 PMNormalSelfTEWARI/FSHEDATB0038 - Tinkharia - BOPSM - 752200ATB - Leaf - 7 - 10.4BGRJ121.8152986.48BGRJ-Leaf-7
2014711/02/2014AllCBL0008BorbamBOP10C130413016525CBL1254250140236Leaf47776801000111/02/2014 4:54:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0008 - Borbam - BOP - 732500CBL - Leaf - 7 - 10.4BGRJ131.8432960.5BGRJ-Leaf-7
2014711/02/2014AllCBL0009BorbamBOPSM10C130713116525CBL125425070243Leaf47776781000111/02/2014 4:54:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0009 - Borbam - BOPSM - 732750CBL - Leaf - 7 - 10.4BGRJ132.8533211.35BGRJ-Leaf-7
2014711/02/2014AllCBL0059NyagograBP10C871422P13616522CBL122422000220Leaf47778261000111/02/2014 5:07:59 PMNormalSHREE VINAYAK TEA ENTERPRISESKANOITEACBL0059 - Nyagogra - BP - 729920CBL - Leaf - 7 - 10.4BGRJ137.8630329.73BGRJ-Leaf-7
2014711/02/2014AllJTC0035DejooBP112C121312212035JTC1422.442000420Leaf47711871200111/02/2014 9:12:00 AMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONEJTC0035 - Dejoo - BP1 - 751240JTC - Leaf - 7 - 10.2BGRJ123.8152002.22BGRJ-Leaf-7
2014711/02/2014AllCBL0011BorbamBOPSM10C130213417025CBL125425000250Leaf47776711000111/02/2014 4:53:00 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0011 - Borbam - BOPSM - 733500CBL - Leaf - 7 - 10.4BPAT137.7834445.5BPAT-Leaf-7
2014711/02/2014AllCBL0019BorbamBP20C127813118026CBL1528520140506Leaf47777292000111/02/2014 4:57:01 PMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONECBL0019 - Borbam - BP - 768120CBL - Leaf - 7 - 10.4BPAT134.7170050.76BPAT-Leaf-7
2014711/02/2014AllCBL0044SuntokBOP15C58913516028CBL142342000420Leaf47777691500111/02/2014 5:03:01 PMNormalSHREE VINAYAK TEA ENTERPRISESBEHERAP1TDRCBL0044 - Suntok - BOP - 756700CBL - Leaf - 7 - 10.2BPAT138.8158298.1BPAT-Leaf-7
2014711/02/2014AllCBL0051BehaliBOP10C161142P13116530CBL130230070293Leaf47777561000111/02/2014 5:01:00 PMNormalSelfOCTAVIUSCBL0051 - Behali - BOP - 739300CBL - Leaf - 7 - 10.2BPAT134.7140413.9BPAT-Leaf-7
2014711/02/2014AllJTC0031DejooBP23C120813215527JTC1630.262100621Leaf47714022300111/02/2014 9:20:59 AMNormalSHREE VINAYAK TEA ENTERPRISESUNITYONEJTC0031 - Dejoo - BP - 781972JTC - Leaf - 7 - 10.4BPAT135.7484292.06BPAT-Leaf-7
2014711/02/2014AllCBL0062PertabghurBP15C241372P13115534CBL151351000510Leaf47778451500111/02/2014 5:08:59 PMNormalSHREE VINAYAK TEA ENTERPRISESITSATDRCBL0062 - Pertabghur - BP - 766810CBL - Leaf - 7 - 10.2GTPP132.5067572.65GTPP-Leaf-7
2014711/02/2014AllCBL0064PertabghurPF6C241375P14014535CBL1211.221000210Leaf4777795600211/02/2014 5:05:00 PMNormalSelfITSATDRCBL0064 - Pertabghur - PF - 729400CBL - Leaf - 7 - 20.2GTPP141.5329720.46GTPP-Leaf-7
2014711/02/2014AllABL0030NamburnadiBP115C513849530ABL145345000450Leaf47758101500111/02/2014 2:44:01 PMNormalSelfJAYSHREEP7TDRABL0030 - Namburnadi - BP1 - 737800ABL - Leaf - 7 - 10.2KKR85.6938558.52KKR-Leaf-7
2014711/02/2014AllABL0039BHUYANKHATBP11C146612514534ABL1380.6374140360Leaf47758611100111/02/2014 2:49:01 PMNormalSHREE VINAYAK TEA ENTERPRISESBEHERAP1TDRABL0039 - BHUYANKHAT - BP - 746750ABL - Leaf - 7 - 10.6KKR126.8347432.55KKR-Leaf-7
2014711/02/2014AllATB0011MokrungPF20C3125415019428ATB156856000560Leaf47773472000111/02/2014 4:20:00 PMNormalSelfBSSONAIATB0011 - Mokrung - PF - 784000ATB - Leaf - 7 - 10.4KKR151.9185069.6KKR-Leaf-7
2014711/02/2014AllCBL0058NyagograBP20C871393P13517023CBL146846000460Leaf47778212000111/02/2014 5:07:00 PMNormalSHREE VINAYAK TEA ENTERPRISESOCTAVIUSCBL0058 - Nyagogra - BP - 762100CBL - Leaf - 7 - 10.4KKR136.8662955.14KKR-Leaf-7
2014711/02/2014AllCBL0092BUDLABETABOP20C188113215021CBL142842000420Leaf47778492000111/02/2014 5:09:59 PMNormalSHREE VINAYAK TEA ENTERPRISESAPEEJAYCBL0092 - BUDLABETA - BOP - 755440CBL - Leaf - 7 - 10.4KKR133.8556216.5KKR-Leaf-7

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=3><COL></COLGROUP>


Thanks for the help
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
for column AI
AI3: =ROUND(J3+(J3*VLOOKUP(AH3,[Rate.xlsx]Rates!$A:$C,2,)/100)+VLOOKUP(AH3,[Rate.xlsx]Rates!$A:$C,3,),2)

assuming that the sheet in workbook Rate is called Rates (else change to suit).

It is possible that the workbook needs to be open in order to calculate properly
 
Upvote 0
Well it worked in a sample workbook I put together. On Monday I'll try to post the workbook.

Why do you want this as VBA? And what do you want the code to do? Just calculate the result and put it ina a variable or cell, or have the macro put the formula into a cell?
 
Upvote 0
I want vba for automation

Also in Dealbook I want to create following forumula in new columns

=AH2&"-"&T2&"-"&B2 (S Bill No)
=AI2*P2 (Amount)

and want to sort final book by S Bill No


 
Upvote 0
Let's get the formula working first, else I can't write it in VBA either:

I have this in my Dealbook sheet:

Excel 2010
IJAGAHAI
2Invoice NoPrice/KgChest WeightPARTYSale Rate
3C513840.2KKR85.69
Dealbook
Cell Formulas
RangeFormula
AI3=ROUND(J3+(J3*VLOOKUP(AH3,[Rate.xlsx]Rates!$A:$C,2,)/100)+VLOOKUP(AH3,[Rate.xlsx]Rates!$A:$C,3,),2)


And this in the external workbook 'Rate.xlsx'


Excel 2010
ABC
1PartyCode%Fix
2KKR0.341.4
3ATCR1.340.4
4BGRJ0.341.4
5CTCA1.340.4
6GTPP0.341.05
7BPAT2.30.7
Rates


And so the formula in column AI of dealbook works.

Where does your's fall down? Are you sure it is pointing to the correct workbook name and correct sheet name?
is the Rate.xlsx open?
 
Upvote 0
Macro is working I had not renamed sheet to Rates. but how to add two more formula and last to sort dealbook by S Bill No.
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> EnterFormulas()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> rIn <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> sPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sFormula <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lLastR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'get path</SPAN><br>    sPath = ThisWorkbook.Path<br>    <br>    <SPAN style="color:#007F00">' get last row of sheet</SPAN><br>    lLastR = Cells(Rows.Count, 1).End(xlUp).Row<br>    <br>    <SPAN style="color:#007F00">' now construct formula and insert into range:</SPAN><br>    sFormula = "=ROUND(J2+(J2*VLOOKUP(AH2,'" & sPath & "\[Rate.xlsx]Rates'!$A:$C,2,)/100)+VLOOKUP(AH2,'" & sPath & "\[Rate.xlsx]Rates'!$A:$C,3,),2)"<br>    <br>    Range("AI2:AI" & lLastR).Formula = sFormula <SPAN style="color:#007F00">' the formula will expand automatically to the correct addresses for the rows below AI3</SPAN><br>    <br>    Range("AJ2:AJ" & lLastR).Formula = "=AI2*P2"<br>    Range("AK2:AK" & lLastR).Formula = "=AH2&""-""&T2&""-""&B2"<br>    <br>    <SPAN style="color:#007F00">' lastly sort the table</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Sort<br>        .SortFields.Add Key:=Range("AK2"), SortOn:=xlSortOnValues, _<br>                Order:=xlAscending, DataOption:=xlSortNormal<br>        .SetRange Range("A2").CurrentRegion<br>        .Header = xlYes<br>        .MatchCase = <SPAN style="color:#00007F">False</SPAN><br>        .Orientation = xlTopToBottom<br>        .SortMethod = xlPinYin<br>        .Apply<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Macro3()</FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
EXCEL VBA or Formula: Split sets £ enteries based on criteria amount or close to it
Replies
0
Views
567
emz07
E
V
  • Question
VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.
Replies
2
Views
4K
vekdas
V
J
  • Question
Change the values of a sheet based on negative and positive calculation of another sheet
Replies
0
Views
949
Jyotirmaya
J
A
  • Question
copy specific data from sheet to another based on condition column value
Replies
6
Views
205
Fluff
Fluff
K
  • Question
Looking for excel formula to sum the column in 2 different sheets.
Replies
2
Views
549
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,215,035
Messages
6,122,785
Members
449,095
Latest member
m_smith_solihull

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