Excel Formula's

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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$1000,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.