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

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

-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


"SDEEEM" wrote:

> 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

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

On Wed, 16 Nov 2005 15:57:04 -0800, SDEEEM <SDEEEM@discussions.microsoft.com>
wrote:

>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

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


"Gord Dibben" wrote:

> 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
>
> On Wed, 16 Nov 2005 15:57:04 -0800, SDEEEM <SDEEEM@discussions.microsoft.com>
> wrote:
>
> >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
>
>