Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Range Name In VLOOKUP
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Range Name In VLOOKUP
Sorry, I accidently had a comman in my formula for this post...I have removed
it "John Calder" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Range Name In VLOOKUP
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 he http://contextures.com/xlNames01.html#Dynamic John Calder wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Range Name In VLOOKUP
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" wrote: 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 he http://contextures.com/xlNames01.html#Dynamic John Calder wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Range Name In VLOOKUP
I don't see anything wrong with using the entire column.
Just make sure you don't put other stuff under your table. John Calder wrote: 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" wrote: 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 he http://contextures.com/xlNames01.html#Dynamic John Calder wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Range | Excel Worksheet Functions | |||
VLookup Range Help | Excel Worksheet Functions | |||
vlookup range changes | Excel Worksheet Functions | |||
Vlookup where range changes | Excel Worksheet Functions | |||
Vlookup - Range | Excel Discussion (Misc queries) |