vlook/match/index

[nowfal]

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

[Bob Phillips]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

Hi!

Assume the table is in sheet2 A1:C8

=IF(OR(C1="",D1=""),"",VLOOKUP(D1,Sheet2!A1:C8,MATCH(C1,{"C","P","S"},0),0))

Biff

"nowfal" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ian]

=IF(C1="P",VLOOKUP(D1,Sheet2!A2:C8,2),IF(C1="S",VLOOKUP(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

Hi Bob,
here is the workbook attached. Have a look.
thanks
nowfal

[Bob Phillips]

No attachment. Mail it to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

Bob,
what is your e-mail ID?

[nowfal]

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

[Bob Phillips]

bob dot phillips at tiscali dot co dot uk

do the obvious

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>