I want to create an excel table with 3 colums.

Category Item Price

Veg Potato 20

Tomato 30

Carrot 10

Onion 15Non Veg Chicken 60

Mutton 70Now when in a new sheet when i enter veg in the first column, second column drop down u should get only 4 items in veg and when selecting one of then should give only price of that in 3rd column

like vice for non-veg.Can i do this in excel without using VB function, if yes can someone help...

Assuming your data looks like this, follow the steps outlined below. A B C 1 Category Item Price 2 Veg Potato 20 3 Tomato 30 4 Carrot 10 5 Onion 15 6 7 NonVeg Chicken 60 8 Mutton 701 - Select B2:C5 and name the Range "Veg"

2 - Select B7:C8 and name the Range "NonVeg"Now, let's assume you want to enter Veg or NonVeg in Sheet2!A1

3a - Select Sheet2!B1 and use Data...Validation...List

3b - In the Source box enter =INDEX(INDIRECT(A1),,1)The INDIRECT function tells Excel that the text in A1 is actually the Name of a Range. The INDEX(

ref,,1) function tells Excel to only use the first column of the Named range as the Source for the Data Validation Drop Down list.4 - In C1 enter this formula:

=VLOOKUP(B1,INDIRECT(A1),2,0)

The INDIRECT function will use the value in A1 as the Range for the VLOOKUP to lookup the value in B1 and return the value from the 2nd column, which should be your price.

Clear? ;-)

Thanks a Lot :)

This is exactly what i wanted to do...

Ask Your Question

Weekly Poll