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
options either PURCHASE(P) or SALE (S) , and the next colomn (D) use
will put currency name like USD or GBP. Now i wanted to fil
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
colomn as 384. Any solution?
thanks and regards
by
NOWFA

 

[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)

 

[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

 

[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.

 

[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]

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]

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

 

[Bob Phillips]

No attachment. Mail it to me.

--

HTH

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

 

[nowfal]

Bob,
what is your e-mail ID

 

[nowfal]

Hi Bob,
Partially solved the problem by including the vlook formul
into my macro. But getting N/A when other colomns are empty, u pl
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 th
above vlookup code.
thanks
nowfa

 

[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)