Excel Formula's
 

Respected Sir
Please help me by providing me a formula relating to my query.
My query is
In coloumn A i have brand name, in column B i have Colour and in Column C i
have rate. As you may call it as price list. That is as soon as i enter brand
name and size (Both) in next sheet the rate from the first sheet should come.

Thanking you all in advance.

Om

Excel Formula's
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:B10" '<== change to suit
Dim iPos As Long
Dim sh As Worksheet

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "A").Value < "" And _
Me.Cells(.Row, "B").Value < "" Then
Set sh = Worksheets("Sheet1")
On Error Resume Next
iPos = Evaluate("Match(1, (Sheet1!A1:A100=""" & _
Me.Cells(.Row, "A").Value & """)*(Sheet1!B1:B100=""" & _
Me.Cells(.Row, "B").Value & """),0)")
On Error GoTo 0
If iPos 0 Then
Me.Cells(.Row, "C").Value = sh.Cells(iPos, "C").Value
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"OM SONI" wrote in message
...
Respected Sir
Please help me by providing me a formula relating to my query.
My query is
In coloumn A i have brand name, in column B i have Colour and in Column C
i
have rate. As you may call it as price list. That is as soon as i enter
brand
name and size (Both) in next sheet the rate from the first sheet should
come.

Thanking you all in advance.

Om

Excel Formula's
 

Hi

Assuming your Pricelist is on Sheet1, and you are entering this data on
Sheet2

In cell C2 of Sheet2 enter the following array formula
{=INDEX(Sheet1!$A$1:$C$100,
MATCH(A2&B2,Sheet1!$A$1:$A$1000&Sheet1!$B$1:$B$100 0,0),3)}

To enter as an array formula, commit or edit using Control+Shift+Enter
(CSE) not just Enter.
Do not type the curly braces { } yourself, if you use CSE Excel will
enter them for you.

Change the ranges to suit the extent of your data.
--
Regards

Roger Govier


"OM SONI" wrote in message
...
Respected Sir
Please help me by providing me a formula relating to my query.
My query is
In coloumn A i have brand name, in column B i have Colour and in
Column C i
have rate. As you may call it as price list. That is as soon as i
enter brand
name and size (Both) in next sheet the rate from the first sheet
should come.

Thanking you all in advance.

Om