Excel create a small database

Microsoft Microsoft excel 2007 full vers...
March 31, 2010 at 12:07:47
Specs: Windows 7
I want to create an excel table with 3 colums.
Category Item Price
Veg Potato 20
Tomato 30
Carrot 10
Onion 15

Non Veg Chicken 60
Mutton 70

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

See More: Excel create a small database

March 31, 2010 at 18:08:10
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
7 NonVeg  Chicken    60
8         Mutton     70 

1 - 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:


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? ;-)

Report •

April 3, 2010 at 23:48:14
Thanks a Lot :)
This is exactly what i wanted to do...

Report •
Related Solutions

Ask Question