Validation list on separate worksheet

Shane

Board Regular
Joined
Apr 8, 2002
Messages
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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!
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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 !

:)
 
Upvote 0
Guys, works perfectly - I'm well impressed!

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

Thanks so much!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top