| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
vlook/match/index
Hi, In the second sheet i have a table as follows: CURRENCY / PURCHASE/ SALE USD ---- 384 ----- 386 GBP ------ 685------- 700 EUR----------- 480-------490 INR-----------8 ----------9 THB----------9 ----------10 AED-------104-----------105 SAR-------102-----------103 In the first sheet I have 6 colomns, In the third("C") colomn i have 2 options either PURCHASE(P) or SALE (S) , and the next colomn (D) user will put currency name like USD or GBP. Now i wanted to fill automatically the respected rates in the colomn(E) ie. If C colomn is P(purchase) and D colomn is USD and i have to get E colomn as 384. Any solution? thanks and regards by NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#2
|
|||
|
|||
|
=VLOOKUP(D2,Sheet2!$A$1:C$20,IF(C2="P",2,3),FALSE)
-- HTH RP (remove nothere from the email address if mailing direct) "nowfal" wrote in message ... Hi, In the second sheet i have a table as follows: CURRENCY / PURCHASE/ SALE USD ---- 384 ----- 386 GBP ------ 685------- 700 EUR----------- 480-------490 INR-----------8 ----------9 THB----------9 ----------10 AED-------104-----------105 SAR-------102-----------103 In the first sheet I have 6 colomns, In the third("C") colomn i have 2 options either PURCHASE(P) or SALE (S) , and the next colomn (D) user will put currency name like USD or GBP. Now i wanted to fill automatically the respected rates in the colomn(E) ie. If C colomn is P(purchase) and D colomn is USD and i have to get E colomn as 384. Any solution? thanks and regards by NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#3
|
|||
|
|||
|
Hi!
Assume the table is in sheet2 A1:C8 =IF(OR(C1="",D1=""),"",VLOOKUP(D1,Sheet2!A1:C8,MAT CH(C1,{"C","P","S"},0),0)) Biff "nowfal" wrote in message ... Hi, In the second sheet i have a table as follows: CURRENCY / PURCHASE/ SALE USD ---- 384 ----- 386 GBP ------ 685------- 700 EUR----------- 480-------490 INR-----------8 ----------9 THB----------9 ----------10 AED-------104-----------105 SAR-------102-----------103 In the first sheet I have 6 colomns, In the third("C") colomn i have 2 options either PURCHASE(P) or SALE (S) , and the next colomn (D) user will put currency name like USD or GBP. Now i wanted to fill automatically the respected rates in the colomn(E) ie. If C colomn is P(purchase) and D colomn is USD and i have to get E colomn as 384. Any solution? thanks and regards by NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#4
|
|||
|
|||
|
=IF(C1="P",VLOOKUP(D1,Sheet2!A2:C8,2),IF(C1="S",VL OOKUP(D1,Sheet2!A2:C8,3),""))
Change Sheet1 & Sheet2 to suit your sheet names. Returns blank cell if C1 is not P or S. I've assumed the data on sheet 2 is in columns A:C with the header in row 1, data staring in row 2. The first column in your data table needs to be in ascending alphanumeric order. -- Ian -- "nowfal" wrote in message ... Hi, In the second sheet i have a table as follows: CURRENCY / PURCHASE/ SALE USD ---- 384 ----- 386 GBP ------ 685------- 700 EUR----------- 480-------490 INR-----------8 ----------9 THB----------9 ----------10 AED-------104-----------105 SAR-------102-----------103 In the first sheet I have 6 colomns, In the third("C") colomn i have 2 options either PURCHASE(P) or SALE (S) , and the next colomn (D) user will put currency name like USD or GBP. Now i wanted to fill automatically the respected rates in the colomn(E) ie. If C colomn is P(purchase) and D colomn is USD and i have to get E colomn as 384. Any solution? thanks and regards by NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#5
|
|||
|
|||
|
Hi bob, It is working perfectly, but i wanted some more help. which is very crucial for to complete the work. The rates shown in the table is the standard rate, it may change or i have to give some better rates to the customer sometimes. so there is a discount limit of both ways plus and minus. I can say as tolerance limit. now the table is as follows CURRENCY / PURCHASE/ SALE/ TOLERANCE LIMIT USD ---- 384 ----- 386 ------------------2 GBP ------ 685------- 700-----------------3 EUR----------- 480-------490--------------5 INR-----------8 ----------9-----------------0.5 THB----------9 ----------10----------------0.3 AED-------104-----------105--------------0.3 SAR-------102-----------103-------------0.5 The problem is i have given the formula in cell E, if i started change the rate or anything on E will erase the formula . Instead my mind is going in the other way create a duplicate row in sheet 2 itself (like the one in sheet 1) and call back . what is your opinion? Is there any other way? I know you can help me , b coz you sorted so many problems earlier too. Others also welcome. thanks in advance with regards NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#6
|
|||
|
|||
|
Nowfal,
Could you not just add the formula to F instead, it works just as well there. I would avoid duplicating it if I possibly could. How will this tolerance rate interact with the actual rates and the formula that you require? -- HTH RP (remove nothere from the email address if mailing direct) "nowfal" wrote in message ... Hi bob, It is working perfectly, but i wanted some more help. which is very crucial for to complete the work. The rates shown in the table is the standard rate, it may change or i have to give some better rates to the customer sometimes. so there is a discount limit of both ways plus and minus. I can say as tolerance limit. now the table is as follows CURRENCY / PURCHASE/ SALE/ TOLERANCE LIMIT USD ---- 384 ----- 386 ------------------2 GBP ------ 685------- 700-----------------3 EUR----------- 480-------490--------------5 INR-----------8 ----------9-----------------0.5 THB----------9 ----------10----------------0.3 AED-------104-----------105--------------0.3 SAR-------102-----------103-------------0.5 The problem is i have given the formula in cell E, if i started change the rate or anything on E will erase the formula . Instead my mind is going in the other way create a duplicate row in sheet 2 itself (like the one in sheet 1) and call back . what is your opinion? Is there any other way? I know you can help me , b coz you sorted so many problems earlier too. Others also welcome. thanks in advance with regards NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#7
|
|||
|
|||
|
Hi Bob, Tolerance is the acceptable limit , the cashier can give to the customer. When a bigger deal comes cashier or the responsible person will give better rate than the normal rate. So, normal purchase rate of USD is 384 , but in this case he may give 385 . Vlook formula brings the rate from the table as 384 but cashier wanted to change to 385 but by mistake or knowingly if he tries to put 390 it should not accept and meantime he is to be warned as 'you are trying out of range' something like that. The same way sale rate also. . I saw these things in an access program but that was quite big program. I already have a work sheet trigger in the particular sheet about customer ID. Again if i put same way means takes lot of time to complete a bill. Bob as u said F, it is not possible b coz in the 'F', total amount is coming (fcy*rate=local amount) that local amount is in F. Thanks Bob u r using your valuable time for this kind of stuff. If you are getting any idea let me know. otherwise if you are permitting i would like to send the worksheet to you,through your personal e-mail. with regards NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#8
|
|||
|
|||
|
Nowfal,
send the worksheet. Add a few comments to show again what should happen. -- HTH RP (remove nothere from the email address if mailing direct) "nowfal" wrote in message ... Hi Bob, Tolerance is the acceptable limit , the cashier can give to the customer. When a bigger deal comes cashier or the responsible person will give better rate than the normal rate. So, normal purchase rate of USD is 384 , but in this case he may give 385 . Vlook formula brings the rate from the table as 384 but cashier wanted to change to 385 but by mistake or knowingly if he tries to put 390 it should not accept and meantime he is to be warned as 'you are trying out of range' something like that. The same way sale rate also. . I saw these things in an access program but that was quite big program. I already have a work sheet trigger in the particular sheet about customer ID. Again if i put same way means takes lot of time to complete a bill. Bob as u said F, it is not possible b coz in the 'F', total amount is coming (fcy*rate=local amount) that local amount is in F. Thanks Bob u r using your valuable time for this kind of stuff. If you are getting any idea let me know. otherwise if you are permitting i would like to send the worksheet to you,through your personal e-mail. with regards NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#9
|
|||
|
|||
|
Hi Bob, here is the workbook attached. Have a look. thanks nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#10
|
|||
|
|||
|
No attachment. Mail it to me.
-- HTH RP (remove nothere from the email address if mailing direct) "nowfal" wrote in message ... Hi Bob, here is the workbook attached. Have a look. thanks nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#11
|
|||
|
|||
|
Bob, what is your e-mail ID? -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#12
|
|||
|
|||
|
Hi Bob, Partially solved the problem by including the vlook formula into my macro. But getting N/A when other colomns are empty, u pls just see the code FCY Macro ' Macro recorded 18/05/2004 by Musandam ' ' Range("A2").Select Worksheets("RECORD").Unprotect Password:="nowfal" Range("A2:AH10000").Select Selection.Copy Range("A3").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False Range("C2:H2,J2:L2,N2,O2:R2,T2:W2,Y2:AB2,AD2:AG2") .ClearContents ActiveWindow.ScrollColumn = 1 Range("l2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],Sheet1!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE)" Worksheets("RECORD").Protect Password:="nowfal" ActiveWorkbook.Save End Sub Further I would like to know how to select multiple range to give the above vlookup code. thanks nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
|
#13
|
|||
|
|||
|
bob dot phillips at tiscali dot co dot uk
do the obvious -- HTH RP (remove nothere from the email address if mailing direct) "nowfal" wrote in message ... Bob, what is your e-mail ID? -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=399926 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
