Range Name In VLOOKUP

[John Calder]

Hi

I use Excel 2000

I would like to use a range name in a VLOOKUOP table

Presently I have 2 worksheets, the 1st one is called ORDER FORM and the 2nd
one is called ADD NEW COLOUR

The ORDER FORM sheet has a data validation list that uses a range name
called "COLOURS" which data range is $A$2:$D$347 on the ADD NEW COLOUR sheet.
This is so that when a new line is inserted into the data on the ADD NEW
COLOUR sheet that the validation box on the ORDER FORM sheet picks up the new
entry.

On the ORDER FORM sheet I have the following:

=VLOOKUP(D15,'ADD NEW COLOUR'!$A$2:$D$347,4))

(D15 is the cell that contains the validation list)

The problem I have is that when a new line of data is inserted into the ADD
NEW COLOURS sheet is that although the validation list picks up the new data
the VLOOKUP does not change to allow for the extra data the tha validation
list provided.

What I would like to know is there a way of including the range name into
the VLOOKUP so that I dont have this problem.

I tried the following without success:

=VLOOKUP(D15,COLOURS,4,))

I hope I have explained this ok

Thanks

 

[John Calder]

Sorry, I accidently had a comman in my formula for this post...I have removed
it

 

[Dave Peterson]

When I do this kind of thing, I like to put the table on a dedicated
worksheet--nothing else on that sheet except for the table.

Then I can use the whole column in my =vlookup() formula:
=VLOOKUP(D15,'ADD NEW COLOUR'!$A:$D,4)

And I'm not sure what you're matching on, but I would think that if it has to do
with part numbers (since it's on order form), that you'd want an exact match:

=VLOOKUP(D15,'ADD NEW COLOUR'!$A:$D,4,0)

=========
If you don't want to use the complete column, maybe you could use a dynamic
range name. Debra Dalgleish explains it here:

http://contextures.com/xlNames01.html#Dynamic

 

[John Calder]

Dave

Thanks for your quick response. The ORDER FORM I am using has a validation
list with a paint colour in it. When selected it puts up 3 adjacent numbers
next to it, one in each column, to the left of the validation box.

As the users will be base level I need to keep the 4 columns of raw data on
a different worksheet.

I had already worked out that dyanamic ranges would somehow play a part in
this problem and have looked at Debra's page. I cant say that at this point I
really understand daynamic ranges....so I have lots of work in front of me.



Thanks

 

[Dave Peterson]

I don't see anything wrong with using the entire column.

Just make sure you don't put other stuff under your table.