I am stumped!! LOL

Posted by Judy on December 23, 2001 4:12 PM

I am trying to create a spreadsheet that has three columns in it as follows: ITEM, POINTS, DESCRIPTION. I want to be able to enter an item number (one of about 100 different items) in the ITEM column, and have the points that relate to that item show up under POINTS and the description of the item show up under DESCRIPTION.

Thus:
ITEM POINTS DESCRIPTION
A1 30 Stereo Install
F5 60 Under Car Neon

I have (I think so anyway) created a range name for each of those three columns, thinking that I would be able to enter a formula in the POINTS column stating If ITEM="A1",30 but that is not working. I think I may have to use set up a table with all my Item numbers, Point value and descriptions and use the VLOOKUP function or something like that but I really do not know for sure if that is the case! GEEZ I feel so braindead!! LOL

Can anyone spare a few minutes to help me with this? It sure would save me alot of time doing one of those "busy work" reports I have to do!!

Thanks! And have a great Holiday Season!

Posted by Rick on December 23, 2001 5:08 PM

Try:
Create the items points and description somewhere out of the way and then name the entire range (I recommend selecting all 3 columns in entirity in case you need to add later) Then in your first point cell enter the formula:

=VLOOKUP($A1,named range,2,FALSE)

Where A1 is the first cell in the Item column where you would enter a value.

Named range is the name you assigned above

2 is the column number where the info you want returned.

For the Description Cells use the same formula except change the 2 to 3.

Then drag the formula down as far as you need it.

Posted by Judy on December 23, 2001 5:59 PM

THANKS RICK!! WORKS GREAT

Rick,

THANKS!! WORKS LIKE A CHARM!! YIPPEE!!

Judy