Lookup Table in Excel? 2

[chris567uk]

Hi
I'd like to have a numeric value apear in column E when a text name is entered in column C. I'd like a table of names/values to be entered on a separate sheet in the same workbook.
IE. if you type "shoes" in cell c5 on sheet 1 and in the table on sheet 2 shoes = 55, then 55 apears in E5 on sheet 1

The Lookup table needs the ability to be updated when a particular text name does not exist in the table.

Can XL do this or is it a job for Access?

Thanks in advance.

Christine.

 

[Digga]

Hi Christine,

VLOOKUP is a good function for this type of thing.

Digga

Sharing Knowledge Saves Valuable Time!

 

[xlbo]

Need more info - the basic idea is easy - as Digga says, VLOOKUP is the function to use
The question is....how do you expect the table to be updated ???

You can use a dynamic named range for the table so that the formulae can remain the same but you will need some kinda interface to update the table..

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[chris567uk]

Thanks Guys
A bit more detail:

Column (x) is headed goods and the name of a range of goods (about 50 but contantly adding and deleting items as new additions to the range of goods or older goods become obsolete)is manually typed in here.

Each type of goods has a rate in monitary value for it's handling.

I want the rate to show up in column (same row as (x) when the type of goods is entered.

For the ease of updating the table, I had a 2 columns in mind - on sheet 2 showing the type of goods in col A and the corresponding rate in col B. This table would be manually updated as new goods come in and older ones deleted.

I'm doing this for a girl at work who doesn't have Access on her computer - let alone knowing how to use it. I know it's easy to do in access but thought id try to do it with XL.

Thanks again
Christine
xx

 

[xlbo]

ok then - still relatively easy
Lets say you have 2 sheets - sheet1 and sheet2
On sheet 2, as you have described, enter the products in col A and prices in col B
Then, go to Insert>Name>Define
Enter a name for the range - lets say myRange
and where you would normally enter a range reference, enter:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),2)
This will create a dynamic named range, starting in A1 and extending to the last row with data in it
Enter anjother named range - this time just for the products - lets call it myProd. the formula this time is:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
On sheet1, lets say you want to enter the product in A1. I would suggest that you use Data>Validation
choose "List"
and in the entry field, enter
=myProd
This will mean that only items in the list of available products can be selected
in B1, enter
=if(A1="","",vlookup(A1,myRange,2,false))
and the price of the product entered in A1 should show up when selected

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[chris567uk]

Geoff, that works perfectly, thanks a million. I had no idea it was so involved. I owe you a drink or two!!

Christine.
xxx

 

[xlbo]

Look me up next time you are in Nottingham for the beers ;-)

It doesn't need to be quite that involved but I reckon it's better to have a slightly more complex setup rather than complex maintenance

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde