For accounting, How to link two lists each in different drop down

[Guest]

I'm trying to create simple program for my little shop sales. I listed all
sale ITEMS and their PRICES in two columns table. I created a drop down list
for the column of ITEMS and what I want exactly is the following:

When I select the ITEM from the drop down list I want to see the price of
the item displayed in the adjacent column.

I know this feature is available in access but I'm not good enough in access.

Regards

 

[Guest]

-On a separate sheet, put the list of all items and their corresponding
prices in 2 adjacent columns.
-Select the whole list
-From the main menu: Insert>Name>Define
-Set the name to: LU_ItemPrice
(The range should already be selected)

Then, for the lookup list of items on your entry sheet, look into Data
Validation:
A1: Item
A2: Data>Data Validation
\Allow: List
\Source: LU_ItemPrice
\Click [OK]
Copy the formula in A2 down as far as needed.

B1: Price
B2: =VLOOKUP(A2,LU_ItemPrice,2,0)

Copy the formula in B2 down as far as needed.

Column A will only allow listed items and column B will display item prices.

Does that help?

••••••••••
Regards,
Ron

 

[Gord Dibben]

SDEEM

Place your ITEMS and PRICES in two adjacent columns on Sheet2.

Select the ITEMS range and Insert>Name>Define.

Name it Itemslist.

Select ITEMS and PRICES range together and give that a name.

Name it LookTable

Select Sheet1 A1 and Data>Validation>List>refers to =Itemslist

In B1 on Sheet1 enter this formula =VLOOKUP(A1,LookTable,2,false)

Pick an item from the drop-down to get a price in B1


Gord Dibben Excel MVP

 

[Guest]

Good point, Gord: 2 range names (items only and items with prices)...not just
1.
I thought it, but didn't type it. I hate it when that happens!

••••••••••
Regards,
Ron