Validation list on separate worksheet

Shane · Apr 9th, 2002, 06:56 AM

Hi! Is it possible to validate cells in one worksheet using a drop-down list where the source data is contained in another worksheet.

At the moment, I have a workbook where I maintain the same validation source list in multiple worksheets rather than one master list (actually I do use a master list but I have lookup lists in the other worksheets.

Thanks!

Anne Troy · Apr 9th, 2002, 07:24 AM

Sure, Shane. Here's what I do:

Select the first value in the validation list. Hit Shift-Ctrl-Down arrow until you're all the way down to row 65,536. Hit Insert-Name-Define and call it something like MyValidList.

When you set the data validation, type exactly this:

=MyValidList

into the range box. Works great!

Shane · Apr 9th, 2002, 08:14 AM

You're absolutely right - thanks a lot!

Chris Davison · Apr 10th, 2002, 01:39 PM

you may run into problems in the future if you delete anything from this list (or, say, accidentally enter something on row 65,536 and then delete it) in so far as it will make the dropdown box massive due to the fact that that last cell has been "validated" (no pun intended). You'll get your list followed by 65,000 rows of blanks !

it might be wise to slightly augment Dreamboat's excellent suggestion by using a dynamic range name rather than the entire column....

if your list sits in column C (for example) name it as she suggests but enter as "refers to" the following :

=OFFSET($C$1,0,0,MATCH(REPT("z",255),$C$C))

the named range will expand and contract accordingly and keep your data validation box blank-free

Yogi Anand · Apr 10th, 2002, 02:49 PM

Quote:

if your list sits in column C (for example) name it as she suggests but enter as "refers to" the following :

=OFFSET($C$1,0,0,MATCH(REPT("z",255),$C$C))

the named range will expand and contract accordingly and keep your data validation box blank-free

Hi Chris:
Your suggested solution augmenting Dreamboat's works beautifully -- Just one editorial correction -- need for added colon at $C$C

=OFFSET($C$1,0,0,MATCH(REPT("z",255),$C:$C))

Shane · Apr 10th, 2002, 03:01 PM

Chris, that's a great suggestion for the perfect solution! A couple of points though.

My list is in column A so in the "Refers To" field I have entered:

=OFFSET($A$1,0,0,MATCH(REPT("z",255),$A$A))

This is causing an error message stating a formula error. Any idea why this may be.

Your formula is impressive (to me at least!). I'd be delighted if you could break it down for me a little and explain so I can understand the logic for future reference - thanks Chris!

Yogi Anand · Apr 10th, 2002, 03:10 PM

Quote:

On 2002-04-10 14:01, Shane wrote:
Chris, that's a great suggestion for the perfect solution! A couple of points though.

My list is in column A so in the "Refers To" field I have entered:

=OFFSET($A$1,0,0,MATCH(REPT("z",255),$A$A))

This is causing an error message stating a formula error. Any idea why this may be.

Your formula is impressive (to me at least!). I'd be delighted if you could break it down for me a little and explain so I can understand the logic for future reference - thanks Chris!

Hi Shane:
First to get you going, change $A$A in Chris' formula to $A:$A -- see my post above.
Then as far as breaking the formula down is concerned, let me become Chris' mouthpiece, although Chris would be best at it --

What the formula is doing is 1)starting the beginning of the list range at cell C1, 2) then it finds the end of the list by matching it to a record that is equal to or smaller than a string of 255 Zs, and it does it dynamically.
I am sure Chris would be delighted to add some more explanation to it!

Chris Davison · Apr 11th, 2002, 01:45 AM

Good spot Yogi buddy!

(You're a hard taskmaster...hee hee)

I couldn't have explained it better myself, so thanks for that, I had long since gone to bed over here...

I should add though, the matching 255 "z" characters was suggested by Mark W and Aladin after I ran into problems with my dynamic ranges, originally from the very useful set that are up on Dave's pages at http://www.ozgrid.com

A beauty from which suggests that by adding a fourth offset arguement ALSO of the match(rept("z",255 etc etc makes your range dynamic horizontally - so you end up with a dynamic range that ebbs and flows not only downwards but along too, just far too handy !

Shane · Apr 11th, 2002, 03:02 AM

Guys, works perfectly - I'm well impressed!

One question, what exactly does the function "REPT" do?

Thanks so much!

Aladin Akyurek · Apr 11th, 2002, 03:04 AM

Quote:

On 2002-04-11 02:02, Shane wrote:
Guys, works perfectly - I'm well impressed!

One question, what exactly does the function "REPT" do?

Thanks so much!

=REPT("z",255)

creates a string of 255 z's.

Aladin

Apr 9th, 2002, 07:24 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Sure, Shane. Here's what I do: Select the first value in the validation list. Hit Shift-Ctrl-Down arrow until you're all the way down to row 65,536. Hit Insert-Name-Define and call it something like MyValidList. When you set the data validation, type exactly this: =MyValidList into the range box. Works great! __________________ ~Anne Troy

Apr 10th, 2002, 01:39 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	you may run into problems in the future if you delete anything from this list (or, say, accidentally enter something on row 65,536 and then delete it) in so far as it will make the dropdown box massive due to the fact that that last cell has been "validated" (no pun intended). You'll get your list followed by 65,000 rows of blanks ! it might be wise to slightly augment Dreamboat's excellent suggestion by using a dynamic range name rather than the entire column.... if your list sits in column C (for example) name it as she suggests but enter as "refers to" the following : =OFFSET($C$1,0,0,MATCH(REPT("z",255),$C$C)) the named range will expand and contract accordingly and keep your data validation box blank-free __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 06:56 AM	#1
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Hi! Is it possible to validate cells in one worksheet using a drop-down list where the source data is contained in another worksheet. At the moment, I have a workbook where I maintain the same validation source list in multiple worksheets rather than one master list (actually I do use a master list but I have lookup lists in the other worksheets. Thanks!

Apr 9th, 2002, 08:14 AM	#3
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	You're absolutely right - thanks a lot!

Apr 10th, 2002, 03:01 PM	#6
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Chris, that's a great suggestion for the perfect solution! A couple of points though. My list is in column A so in the "Refers To" field I have entered: =OFFSET($A$1,0,0,MATCH(REPT("z",255),$A$A)) This is causing an error message stating a formula error. Any idea why this may be. Your formula is impressive (to me at least!). I'd be delighted if you could break it down for me a little and explain so I can understand the logic for future reference - thanks Chris!

Apr 11th, 2002, 01:45 AM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Good spot Yogi buddy! (You're a hard taskmaster...hee hee) I couldn't have explained it better myself, so thanks for that, I had long since gone to bed over here... I should add though, the matching 255 "z" characters was suggested by Mark W and Aladin after I ran into problems with my dynamic ranges, originally from the very useful set that are up on Dave's pages at http://www.ozgrid.com A beauty from which suggests that by adding a fourth offset arguement ALSO of the match(rept("z",255 etc etc makes your range dynamic horizontally - so you end up with a dynamic range that ebbs and flows not only downwards but along too, just far too handy !

Apr 11th, 2002, 03:02 AM	#9
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Guys, works perfectly - I'm well impressed! One question, what exactly does the function "REPT" do? Thanks so much!