Data Validation

[peterm]

Hi All
I have a data validation list which looks up data from a table
when I add another product to the table How do I have it show up in the data
list without having to go back to data validation and change cell ref:
Cheers
peterm

[Springbok]

Hi Peter,

The range should automatically update if you insert a new line between
existing data in the range. Alternatively, use a larger range in yopur
validation and tick the option "ignore blank".

Cheers,
Jon

"peterm" wrote:

> Hi All
> I have a data validation list which looks up data from a table
> when I add another product to the table How do I have it show up in the data
> list without having to go back to data validation and change cell ref:
> Cheers
> peterm

[Biff]

Hi!

Use a dynamic named range.

Assume the source for the dropdown is in column G1:Gn.

Goto Insert>Name>Define
Name: List
Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)

As the source for the dropdown enter =List

Biff

>-----Original Message-----
>Hi All
>I have a data validation list which looks up data from a
table
>when I add another product to the table How do I have it
show up in the data
>list without having to go back to data validation and
change cell ref:
>Cheers
>peterm
>.
>

[Springbok]

Yeh, that's quite a slick way of doing it. Nice one, think I might use that
in future myself...

"Biff" wrote:

> Hi!
>
> Use a dynamic named range.
>
> Assume the source for the dropdown is in column G1:Gn.
>
> Goto Insert>Name>Define
> Name: List
> Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>
> As the source for the dropdown enter =List
>
> Biff
>
> >-----Original Message-----
> >Hi All
> >I have a data validation list which looks up data from a
> table
> >when I add another product to the table How do I have it
> show up in the data
> >list without having to go back to data validation and
> change cell ref:
> >Cheers
> >peterm
> >.
> >
>

[peterm]

Thanks biff & springbok
Peterm

"Biff" wrote:

> Hi!
>
> Use a dynamic named range.
>
> Assume the source for the dropdown is in column G1:Gn.
>
> Goto Insert>Name>Define
> Name: List
> Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>
> As the source for the dropdown enter =List
>
> Biff
>
> >-----Original Message-----
> >Hi All
> >I have a data validation list which looks up data from a
> table
> >when I add another product to the table How do I have it
> show up in the data
> >list without having to go back to data validation and
> change cell ref:
> >Cheers
> >peterm
> >.
> >
>

[peterm]

This worked but now the second row of my lookup box won't show

"Biff" wrote:

> Hi!
>
> Use a dynamic named range.
>
> Assume the source for the dropdown is in column G1:Gn.
>
> Goto Insert>Name>Define
> Name: List
> Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>
> As the source for the dropdown enter =List
>
> Biff
>
> >-----Original Message-----
> >Hi All
> >I have a data validation list which looks up data from a
> table
> >when I add another product to the table How do I have it
> show up in the data
> >list without having to go back to data validation and
> change cell ref:
> >Cheers
> >peterm
> >.
> >
>

[Debra Dalgleish]

In your list, are there any blank cells? Since the formula is counting
cells with data in column G, blank cells in the would cause a problem.

peterm wrote:
> This worked but now the second row of my lookup box won't show
>
> "Biff" wrote:
>
>
>>Hi!
>>
>>Use a dynamic named range.
>>
>>Assume the source for the dropdown is in column G1:Gn.
>>
>>Goto Insert>Name>Define
>>Name: List
>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>
>>As the source for the dropdown enter =List
>>
>>Biff
>>
>>
>>>-----Original Message-----
>>>Hi All
>>>I have a data validation list which looks up data from a
>>
>>table
>>
>>>when I add another product to the table How do I have it
>>
>>show up in the data
>>
>>>list without having to go back to data validation and
>>
>>change cell ref:
>>
>>>Cheers
>>>peterm
>>>.
>>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[peterm]

Hi Debra
I don't have any blank cells & the table is sorted correctly.
I have 3 columns in my table the 1st has the code (which I use in the data
validation) The 2nd is the product & the 3rd is the price all worked untill I
added the COUNTA code.
Cheers peter


"Debra Dalgleish" wrote:

> In your list, are there any blank cells? Since the formula is counting
> cells with data in column G, blank cells in the would cause a problem.
>
> peterm wrote:
> > This worked but now the second row of my lookup box won't show
> >
> > "Biff" wrote:
> >
> >
> >>Hi!
> >>
> >>Use a dynamic named range.
> >>
> >>Assume the source for the dropdown is in column G1:Gn.
> >>
> >>Goto Insert>Name>Define
> >>Name: List
> >>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
> >>
> >>As the source for the dropdown enter =List
> >>
> >>Biff
> >>
> >>
> >>>-----Original Message-----
> >>>Hi All
> >>>I have a data validation list which looks up data from a
> >>
> >>table
> >>
> >>>when I add another product to the table How do I have it
> >>
> >>show up in the data
> >>
> >>>list without having to go back to data validation and
> >>
> >>change cell ref:
> >>
> >>>Cheers
> >>>peterm
> >>>.
> >>>
> >>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

I'm confused. Previously you mentioned a missing row in the list, but
now you're mentioning adjacent columns that aren't working.

In which column is the list of codes, and what formula did you use to
define the dynamic range for codes?

Did you create dynamic ranges for the product and price? What formulas
were used for those?

Is there anything else in those columns, below or above the product table?

peterm wrote:
> Hi Debra
> I don't have any blank cells & the table is sorted correctly.
> I have 3 columns in my table the 1st has the code (which I use in the data
> validation) The 2nd is the product & the 3rd is the price all worked untill I
> added the COUNTA code.
> Cheers peter
>
>
> "Debra Dalgleish" wrote:
>
>
>>In your list, are there any blank cells? Since the formula is counting
>>cells with data in column G, blank cells in the would cause a problem.
>>
>>peterm wrote:
>>
>>>This worked but now the second row of my lookup box won't show
>>>
>>>"Biff" wrote:
>>>
>>>
>>>
>>>>Hi!
>>>>
>>>>Use a dynamic named range.
>>>>
>>>>Assume the source for the dropdown is in column G1:Gn.
>>>>
>>>>Goto Insert>Name>Define
>>>>Name: List
>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>>>
>>>>As the source for the dropdown enter =List
>>>>
>>>>Biff
>>>>
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>Hi All
>>>>>I have a data validation list which looks up data from a
>>>>
>>>>table
>>>>
>>>>
>>>>>when I add another product to the table How do I have it
>>>>
>>>>show up in the data
>>>>
>>>>
>>>>>list without having to go back to data validation and
>>>>
>>>>change cell ref:
>>>>
>>>>
>>>>>Cheers
>>>>>peterm
>>>>>.
>>>>>
>>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[peterm]

Hi Debra
Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
code eg (fs) 2nd with product (FlyScreen) 3rd with price
I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
",VLOOKUP(A16,Style,2)).
I want to add extra products to the table and have the code appear in the
validation list automatically
Sorry for the confuseion
Peterm

"Debra Dalgleish" wrote:

> I'm confused. Previously you mentioned a missing row in the list, but
> now you're mentioning adjacent columns that aren't working.
>
> In which column is the list of codes, and what formula did you use to
> define the dynamic range for codes?
>
> Did you create dynamic ranges for the product and price? What formulas
> were used for those?
>
> Is there anything else in those columns, below or above the product table?
>
> peterm wrote:
> > Hi Debra
> > I don't have any blank cells & the table is sorted correctly.
> > I have 3 columns in my table the 1st has the code (which I use in the data
> > validation) The 2nd is the product & the 3rd is the price all worked untill I
> > added the COUNTA code.
> > Cheers peter
> >
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>In your list, are there any blank cells? Since the formula is counting
> >>cells with data in column G, blank cells in the would cause a problem.
> >>
> >>peterm wrote:
> >>
> >>>This worked but now the second row of my lookup box won't show
> >>>
> >>>"Biff" wrote:
> >>>
> >>>
> >>>
> >>>>Hi!
> >>>>
> >>>>Use a dynamic named range.
> >>>>
> >>>>Assume the source for the dropdown is in column G1:Gn.
> >>>>
> >>>>Goto Insert>Name>Define
> >>>>Name: List
> >>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
> >>>>
> >>>>As the source for the dropdown enter =List
> >>>>
> >>>>Biff
> >>>>
> >>>>
> >>>>
> >>>>>-----Original Message-----
> >>>>>Hi All
> >>>>>I have a data validation list which looks up data from a
> >>>>
> >>>>table
> >>>>
> >>>>
> >>>>>when I add another product to the table How do I have it
> >>>>
> >>>>show up in the data
> >>>>
> >>>>
> >>>>>list without having to go back to data validation and
> >>>>
> >>>>change cell ref:
> >>>>
> >>>>
> >>>>>Cheers
> >>>>>peterm
> >>>>>.
> >>>>>
> >>>>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You should have a dynamic range for the codes, e.g.:
=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)

And a dynamic range for the product table (Style):
=OFFSET(Codes,,,,3)

Use the range Codes for the data validation list source.

In the VLOOKUP formula, add FALSE as the fourth argument, unless your
list will be in alphabetical order:

=IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
VLOOKUP(A16,Style,2,FALSE))

peterm wrote:
> Hi Debra
> Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
> code eg (fs) 2nd with product (FlyScreen) 3rd with price
> I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
> ",VLOOKUP(A16,Style,2)).
> I want to add extra products to the table and have the code appear in the
> validation list automatically
> Sorry for the confuseion
> Peterm
>
> "Debra Dalgleish" wrote:
>
>
>>I'm confused. Previously you mentioned a missing row in the list, but
>>now you're mentioning adjacent columns that aren't working.
>>
>>In which column is the list of codes, and what formula did you use to
>>define the dynamic range for codes?
>>
>>Did you create dynamic ranges for the product and price? What formulas
>>were used for those?
>>
>>Is there anything else in those columns, below or above the product table?
>>
>>peterm wrote:
>>
>>>Hi Debra
>>>I don't have any blank cells & the table is sorted correctly.
>>>I have 3 columns in my table the 1st has the code (which I use in the data
>>>validation) The 2nd is the product & the 3rd is the price all worked untill I
>>>added the COUNTA code.
>>>Cheers peter
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>In your list, are there any blank cells? Since the formula is counting
>>>>cells with data in column G, blank cells in the would cause a problem.
>>>>
>>>>peterm wrote:
>>>>
>>>>
>>>>>This worked but now the second row of my lookup box won't show
>>>>>
>>>>>"Biff" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Hi!
>>>>>>
>>>>>>Use a dynamic named range.
>>>>>>
>>>>>>Assume the source for the dropdown is in column G1:Gn.
>>>>>>
>>>>>>Goto Insert>Name>Define
>>>>>>Name: List
>>>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>>>>>
>>>>>>As the source for the dropdown enter =List
>>>>>>
>>>>>>Biff
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>-----Original Message-----
>>>>>>>Hi All
>>>>>>>I have a data validation list which looks up data from a
>>>>>>
>>>>>>table
>>>>>>
>>>>>>
>>>>>>
>>>>>>>when I add another product to the table How do I have it
>>>>>>
>>>>>>show up in the data
>>>>>>
>>>>>>
>>>>>>
>>>>>>>list without having to go back to data validation and
>>>>>>
>>>>>>change cell ref:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Cheers
>>>>>>>peterm
>>>>>>>.
>>>>>>>
>>>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[peterm]

Hi Debra
Hope i'm not driving you crazy like this is to me.
Have renamed using dynamic range but if I use the false in the lookup
nothing works,take out the false and of course it returns the wrong value
some of the time but not always. eg cs returns crimsafe - correct, tst1
returns test6 -incorrect should be test1
Cheers peter

"Debra Dalgleish" wrote:

> You should have a dynamic range for the codes, e.g.:
> =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
>
> And a dynamic range for the product table (Style):
> =OFFSET(Codes,,,,3)
>
> Use the range Codes for the data validation list source.
>
> In the VLOOKUP formula, add FALSE as the fourth argument, unless your
> list will be in alphabetical order:
>
> =IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
> VLOOKUP(A16,Style,2,FALSE))
>
> peterm wrote:
> > Hi Debra
> > Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
> > code eg (fs) 2nd with product (FlyScreen) 3rd with price
> > I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
> > ",VLOOKUP(A16,Style,2)).
> > I want to add extra products to the table and have the code appear in the
> > validation list automatically
> > Sorry for the confuseion
> > Peterm
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>I'm confused. Previously you mentioned a missing row in the list, but
> >>now you're mentioning adjacent columns that aren't working.
> >>
> >>In which column is the list of codes, and what formula did you use to
> >>define the dynamic range for codes?
> >>
> >>Did you create dynamic ranges for the product and price? What formulas
> >>were used for those?
> >>
> >>Is there anything else in those columns, below or above the product table?
> >>
> >>peterm wrote:
> >>
> >>>Hi Debra
> >>>I don't have any blank cells & the table is sorted correctly.
> >>>I have 3 columns in my table the 1st has the code (which I use in the data
> >>>validation) The 2nd is the product & the 3rd is the price all worked untill I
> >>>added the COUNTA code.
> >>>Cheers peter
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>In your list, are there any blank cells? Since the formula is counting
> >>>>cells with data in column G, blank cells in the would cause a problem.
> >>>>
> >>>>peterm wrote:
> >>>>
> >>>>
> >>>>>This worked but now the second row of my lookup box won't show
> >>>>>
> >>>>>"Biff" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hi!
> >>>>>>
> >>>>>>Use a dynamic named range.
> >>>>>>
> >>>>>>Assume the source for the dropdown is in column G1:Gn.
> >>>>>>
> >>>>>>Goto Insert>Name>Define
> >>>>>>Name: List
> >>>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
> >>>>>>
> >>>>>>As the source for the dropdown enter =List
> >>>>>>
> >>>>>>Biff
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>-----Original Message-----
> >>>>>>>Hi All
> >>>>>>>I have a data validation list which looks up data from a
> >>>>>>
> >>>>>>table
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>when I add another product to the table How do I have it
> >>>>>>
> >>>>>>show up in the data
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>list without having to go back to data validation and
> >>>>>>
> >>>>>>change cell ref:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>Cheers
> >>>>>>>peterm
> >>>>>>>.
> >>>>>>>
> >>>>>>
> >>>>--
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

If you'd like to send me a small sample of your file, I can take a look.
Remove the capital letters from my email address.

peterm wrote:
> Hi Debra
> Hope i'm not driving you crazy like this is to me.
> Have renamed using dynamic range but if I use the false in the lookup
> nothing works,take out the false and of course it returns the wrong value
> some of the time but not always. eg cs returns crimsafe - correct, tst1
> returns test6 -incorrect should be test1
> Cheers peter
>
> "Debra Dalgleish" wrote:
>
>
>>You should have a dynamic range for the codes, e.g.:
>> =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
>>
>>And a dynamic range for the product table (Style):
>> =OFFSET(Codes,,,,3)
>>
>>Use the range Codes for the data validation list source.
>>
>>In the VLOOKUP formula, add FALSE as the fourth argument, unless your
>>list will be in alphabetical order:
>>
>> =IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
>> VLOOKUP(A16,Style,2,FALSE))
>>
>>peterm wrote:
>>
>>>Hi Debra
>>>Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
>>>code eg (fs) 2nd with product (FlyScreen) 3rd with price
>>> I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
>>>",VLOOKUP(A16,Style,2)).
>>>I want to add extra products to the table and have the code appear in the
>>>validation list automatically
>>>Sorry for the confuseion
>>>Peterm
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>I'm confused. Previously you mentioned a missing row in the list, but
>>>>now you're mentioning adjacent columns that aren't working.
>>>>
>>>>In which column is the list of codes, and what formula did you use to
>>>>define the dynamic range for codes?
>>>>
>>>>Did you create dynamic ranges for the product and price? What formulas
>>>>were used for those?
>>>>
>>>>Is there anything else in those columns, below or above the product table?
>>>>
>>>>peterm wrote:
>>>>
>>>>
>>>>>Hi Debra
>>>>>I don't have any blank cells & the table is sorted correctly.
>>>>>I have 3 columns in my table the 1st has the code (which I use in the data
>>>>>validation) The 2nd is the product & the 3rd is the price all worked untill I
>>>>>added the COUNTA code.
>>>>>Cheers peter
>>>>>
>>>>>
>>>>>"Debra Dalgleish" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>In your list, are there any blank cells? Since the formula is counting
>>>>>>cells with data in column G, blank cells in the would cause a problem.
>>>>>>
>>>>>>peterm wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>This worked but now the second row of my lookup box won't show
>>>>>>>
>>>>>>>"Biff" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Hi!
>>>>>>>>
>>>>>>>>Use a dynamic named range.
>>>>>>>>
>>>>>>>>Assume the source for the dropdown is in column G1:Gn.
>>>>>>>>
>>>>>>>>Goto Insert>Name>Define
>>>>>>>>Name: List
>>>>>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>>>>>>>
>>>>>>>>As the source for the dropdown enter =List
>>>>>>>>
>>>>>>>>Biff
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>-----Original Message-----
>>>>>>>>>Hi All
>>>>>>>>>I have a data validation list which looks up data from a
>>>>>>>>
>>>>>>>>table
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>when I add another product to the table How do I have it
>>>>>>>>
>>>>>>>>show up in the data
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>list without having to go back to data validation and
>>>>>>>>
>>>>>>>>change cell ref:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Cheers
>>>>>>>>>peterm
>>>>>>>>>.
>>>>>>>>>
>>>>>>>>
>>>>>>--
>>>>>>Debra Dalgleish
>>>>>>Excel FAQ, Tips & Book List
>>>>>>http://www.contextures.com/tiptech.html
>>>>>>
>>>>>>
>>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[peterm]

Thanks for that Debra
Have zipped the file down and put all on 1 page to keep it small
Cheers
peterm


"Debra Dalgleish" wrote:

> If you'd like to send me a small sample of your file, I can take a look.
> Remove the capital letters from my email address.
>
> peterm wrote:
> > Hi Debra
> > Hope i'm not driving you crazy like this is to me.
> > Have renamed using dynamic range but if I use the false in the lookup
> > nothing works,take out the false and of course it returns the wrong value
> > some of the time but not always. eg cs returns crimsafe - correct, tst1
> > returns test6 -incorrect should be test1
> > Cheers peter
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>You should have a dynamic range for the codes, e.g.:
> >> =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
> >>
> >>And a dynamic range for the product table (Style):
> >> =OFFSET(Codes,,,,3)
> >>
> >>Use the range Codes for the data validation list source.
> >>
> >>In the VLOOKUP formula, add FALSE as the fourth argument, unless your
> >>list will be in alphabetical order:
> >>
> >> =IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
> >> VLOOKUP(A16,Style,2,FALSE))
> >>
> >>peterm wrote:
> >>
> >>>Hi Debra
> >>>Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
> >>>code eg (fs) 2nd with product (FlyScreen) 3rd with price
> >>> I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
> >>>",VLOOKUP(A16,Style,2)).
> >>>I want to add extra products to the table and have the code appear in the
> >>>validation list automatically
> >>>Sorry for the confuseion
> >>>Peterm
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>I'm confused. Previously you mentioned a missing row in the list, but
> >>>>now you're mentioning adjacent columns that aren't working.
> >>>>
> >>>>In which column is the list of codes, and what formula did you use to
> >>>>define the dynamic range for codes?
> >>>>
> >>>>Did you create dynamic ranges for the product and price? What formulas
> >>>>were used for those?
> >>>>
> >>>>Is there anything else in those columns, below or above the product table?
> >>>>
> >>>>peterm wrote:
> >>>>
> >>>>
> >>>>>Hi Debra
> >>>>>I don't have any blank cells & the table is sorted correctly.
> >>>>>I have 3 columns in my table the 1st has the code (which I use in the data
> >>>>>validation) The 2nd is the product & the 3rd is the price all worked untill I
> >>>>>added the COUNTA code.
> >>>>>Cheers peter
> >>>>>
> >>>>>
> >>>>>"Debra Dalgleish" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>In your list, are there any blank cells? Since the formula is counting
> >>>>>>cells with data in column G, blank cells in the would cause a problem.
> >>>>>>
> >>>>>>peterm wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>This worked but now the second row of my lookup box won't show
> >>>>>>>
> >>>>>>>"Biff" wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>Hi!
> >>>>>>>>
> >>>>>>>>Use a dynamic named range.
> >>>>>>>>
> >>>>>>>>Assume the source for the dropdown is in column G1:Gn.
> >>>>>>>>
> >>>>>>>>Goto Insert>Name>Define
> >>>>>>>>Name: List
> >>>>>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
> >>>>>>>>
> >>>>>>>>As the source for the dropdown enter =List
> >>>>>>>>
> >>>>>>>>Biff
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>-----Original Message-----
> >>>>>>>>>Hi All
> >>>>>>>>>I have a data validation list which looks up data from a
> >>>>>>>>
> >>>>>>>>table
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>when I add another product to the table How do I have it
> >>>>>>>>
> >>>>>>>>show up in the data
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>list without having to go back to data validation and
> >>>>>>>>
> >>>>>>>>change cell ref:
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>Cheers
> >>>>>>>>>peterm
> >>>>>>>>>.
> >>>>>>>>>
> >>>>>>>>
> >>>>>>--
> >>>>>>Debra Dalgleish
> >>>>>>Excel FAQ, Tips & Book List
> >>>>>>http://www.contextures.com/tiptech.html
> >>>>>>
> >>>>>>
> >>>>>
> >>>>--
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

I haven't received your file -- did you remove the XSPAM from my address?

peterm wrote:
> Thanks for that Debra
> Have zipped the file down and put all on 1 page to keep it small
> Cheers
> peterm
>
>
> "Debra Dalgleish" wrote:
>
>
>>If you'd like to send me a small sample of your file, I can take a look.
>>Remove the capital letters from my email address.
>>
>>peterm wrote:
>>
>>>Hi Debra
>>>Hope i'm not driving you crazy like this is to me.
>>>Have renamed using dynamic range but if I use the false in the lookup
>>>nothing works,take out the false and of course it returns the wrong value
>>>some of the time but not always. eg cs returns crimsafe - correct, tst1
>>>returns test6 -incorrect should be test1
>>>Cheers peter
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>You should have a dynamic range for the codes, e.g.:
>>>> =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
>>>>
>>>>And a dynamic range for the product table (Style):
>>>> =OFFSET(Codes,,,,3)
>>>>
>>>>Use the range Codes for the data validation list source.
>>>>
>>>>In the VLOOKUP formula, add FALSE as the fourth argument, unless your
>>>>list will be in alphabetical order:
>>>>
>>>> =IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
>>>> VLOOKUP(A16,Style,2,FALSE))
>>>>
>>>>peterm wrote:
>>>>
>>>>
>>>>>Hi Debra
>>>>>Sorry My Mistake. I have a table(named style) that has 3 columns 1st with
>>>>>code eg (fs) 2nd with product (FlyScreen) 3rd with price
>>>>> I was using =IF(ISERROR(VLOOKUP(A16,Style,2)),"
>>>>>",VLOOKUP(A16,Style,2)).
>>>>>I want to add extra products to the table and have the code appear in the
>>>>>validation list automatically
>>>>>Sorry for the confuseion
>>>>>Peterm
>>>>>
>>>>>"Debra Dalgleish" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>I'm confused. Previously you mentioned a missing row in the list, but
>>>>>>now you're mentioning adjacent columns that aren't working.
>>>>>>
>>>>>>In which column is the list of codes, and what formula did you use to
>>>>>>define the dynamic range for codes?
>>>>>>
>>>>>>Did you create dynamic ranges for the product and price? What formulas
>>>>>>were used for those?
>>>>>>
>>>>>>Is there anything else in those columns, below or above the product table?
>>>>>>
>>>>>>peterm wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi Debra
>>>>>>>I don't have any blank cells & the table is sorted correctly.
>>>>>>>I have 3 columns in my table the 1st has the code (which I use in the data
>>>>>>>validation) The 2nd is the product & the 3rd is the price all worked untill I
>>>>>>>added the COUNTA code.
>>>>>>>Cheers peter
>>>>>>>
>>>>>>>
>>>>>>>"Debra Dalgleish" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>In your list, are there any blank cells? Since the formula is counting
>>>>>>>>cells with data in column G, blank cells in the would cause a problem.
>>>>>>>>
>>>>>>>>peterm wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>This worked but now the second row of my lookup box won't show
>>>>>>>>>
>>>>>>>>>"Biff" wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>Hi!
>>>>>>>>>>
>>>>>>>>>>Use a dynamic named range.
>>>>>>>>>>
>>>>>>>>>>Assume the source for the dropdown is in column G1:Gn.
>>>>>>>>>>
>>>>>>>>>>Goto Insert>Name>Define
>>>>>>>>>>Name: List
>>>>>>>>>>Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>>>>>>>>>
>>>>>>>>>>As the source for the dropdown enter =List
>>>>>>>>>>
>>>>>>>>>>Biff
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>-----Original Message-----
>>>>>>>>>>>Hi All
>>>>>>>>>>>I have a data validation list which looks up data from a
>>>>>>>>>>
>>>>>>>>>>table
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>when I add another product to the table How do I have it
>>>>>>>>>>
>>>>>>>>>>show up in the data
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>list without having to go back to data validation and
>>>>>>>>>>
>>>>>>>>>>change cell ref:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>Cheers
>>>>>>>>>>>peterm
>>>>>>>>>>>.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>--
>>>>>>>>Debra Dalgleish
>>>>>>>>Excel FAQ, Tips & Book List
>>>>>>>>http://www.contextures.com/tiptech.html
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>--
>>>>>>Debra Dalgleish
>>>>>>Excel FAQ, Tips & Book List
>>>>>>http://www.contextures.com/tiptech.html
>>>>>>
>>>>>>
>>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Debra Dalgleish]

Peter,

You had correctly defined a dynamic range named Code, that you needed
for the data validation list. However, to return the matching prices,
using VLOOKUP, you'll need a multicolumn range.

Define a range named CodeLookup, with the definition:

=OFFSET(Code,0,,,3)

Use this in the VLOOKUPs, e.g.:

=IF(ISERROR(VLOOKUP(A16,CodeLookup,2,FALSE))," ",
VLOOKUP(A16,CodeLookup,2,FALSE))

Debra

Debra Dalgleish wrote:
> I haven't received your file -- did you remove the XSPAM from my address?
>
> peterm wrote:
>
>> Thanks for that Debra
>> Have zipped the file down and put all on 1 page to keep it small
>> Cheers
>> peterm
>>
>>
>> "Debra Dalgleish" wrote:
>>
>>
>>> If you'd like to send me a small sample of your file, I can take a look.
>>> Remove the capital letters from my email address.
>>>
>>> peterm wrote:
>>>
>>>> Hi Debra Hope i'm not driving you crazy like this is to me.
>>>> Have renamed using dynamic range but if I use the false in the
>>>> lookup nothing works,take out the false and of course it returns the
>>>> wrong value some of the time but not always. eg cs returns crimsafe
>>>> - correct, tst1 returns test6 -incorrect should be test1
>>>> Cheers peter
>>>>
>>>> "Debra Dalgleish" wrote:
>>>>
>>>>
>>>>
>>>>> You should have a dynamic range for the codes, e.g.:
>>>>> =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
>>>>>
>>>>> And a dynamic range for the product table (Style):
>>>>> =OFFSET(Codes,,,,3)
>>>>>
>>>>> Use the range Codes for the data validation list source.
>>>>>
>>>>> In the VLOOKUP formula, add FALSE as the fourth argument, unless
>>>>> your list will be in alphabetical order:
>>>>>
>>>>> =IF(ISERROR(VLOOKUP(A16,Style,2,FALSE))," ",
>>>>> VLOOKUP(A16,Style,2,FALSE))
>>>>>
>>>>> peterm wrote:
>>>>>
>>>>>
>>>>>> Hi Debra
>>>>>> Sorry My Mistake. I have a table(named style) that has 3 columns
>>>>>> 1st with code eg (fs) 2nd with product (FlyScreen) 3rd with
>>>>>> price I was using
>>>>>> =IF(ISERROR(VLOOKUP(A16,Style,2))," ",VLOOKUP(A16,Style,2)).
>>>>>> I want to add extra products to the table and have the code appear
>>>>>> in the validation list automatically
>>>>>> Sorry for the confuseion
>>>>>> Peterm
>>>>>>
>>>>>> "Debra Dalgleish" wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>> I'm confused. Previously you mentioned a missing row in the list,
>>>>>>> but now you're mentioning adjacent columns that aren't working.
>>>>>>>
>>>>>>> In which column is the list of codes, and what formula did you
>>>>>>> use to define the dynamic range for codes?
>>>>>>>
>>>>>>> Did you create dynamic ranges for the product and price? What
>>>>>>> formulas were used for those?
>>>>>>>
>>>>>>> Is there anything else in those columns, below or above the
>>>>>>> product table?
>>>>>>>
>>>>>>> peterm wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> Hi Debra
>>>>>>>> I don't have any blank cells & the table is sorted correctly.
>>>>>>>> I have 3 columns in my table the 1st has the code (which I use
>>>>>>>> in the data validation) The 2nd is the product & the 3rd is the
>>>>>>>> price all worked untill I added the COUNTA code.
>>>>>>>> Cheers peter
>>>>>>>>
>>>>>>>>
>>>>>>>> "Debra Dalgleish" wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>> In your list, are there any blank cells? Since the formula is
>>>>>>>>> counting cells with data in column G, blank cells in the would
>>>>>>>>> cause a problem.
>>>>>>>>>
>>>>>>>>> peterm wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> This worked but now the second row of my lookup box won't show
>>>>>>>>>>
>>>>>>>>>> "Biff" wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> Hi!
>>>>>>>>>>>
>>>>>>>>>>> Use a dynamic named range.
>>>>>>>>>>>
>>>>>>>>>>> Assume the source for the dropdown is in column G1:Gn.
>>>>>>>>>>>
>>>>>>>>>>> Goto Insert>Name>Define
>>>>>>>>>>> Name: List
>>>>>>>>>>> Refers to: =OFFSET($G$1,0,0,COUNTA($G:$G),1)
>>>>>>>>>>>
>>>>>>>>>>> As the source for the dropdown enter =List
>>>>>>>>>>>
>>>>>>>>>>> Biff
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> -----Original Message-----
>>>>>>>>>>>> Hi All
>>>>>>>>>>>> I have a data validation list which looks up data from a
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> table
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> when I add another product to the table How do I have it
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> show up in the data
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> list without having to go back to data validation and
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> change cell ref:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> Cheers
>>>>>>>>>>>> peterm .
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Debra Dalgleish
>>>>>>>>> Excel FAQ, Tips & Book List
>>>>>>>>> http://www.contextures.com/tiptech.html
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>> --
>>>>>>> Debra Dalgleish
>>>>>>> Excel FAQ, Tips & Book List
>>>>>>> http://www.contextures.com/tiptech.html
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> Debra Dalgleish
>>>>> Excel FAQ, Tips & Book List
>>>>> http://www.contextures.com/tiptech.html
>>>>>
>>>>>
>>>>
>>>
>>> --
>>> Debra Dalgleish
>>> Excel FAQ, Tips & Book List
>>> http://www.contextures.com/tiptech.html
>>>
>>>
>>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html