Data Validation

P

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
 
S

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
 
B

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
 
S

Springbok

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

peterm

Thanks biff & springbok
Peterm

Biff said:
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
Click to expand...
 
D

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.
 
P

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
 
D

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?
 
P

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
 
D

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))
 
P

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
 
D

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.
 
P

peterm

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

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
 
Top