Excel Validation, rule on another sheet

[MarkBeck]

Hi!

I often run into situations where a column takes its Validation list from another worksheet. the problem is that the column will accept other (Invadid) entries too.

Example; "Transactions" sheet takes its "names" (Column D) from a range called "ValidNames" in the "Names" sheet. Column D will list all entries in "ValidNames" but will also permit other entries. This is not the case when the validation list is on the same sheet.

I HAVE ALREADY TRIED THE HELP FILE A MILLION TIMES BUT I DONT UNDERSTANT IT!

Thank you

Mark

 

[SkipVought]

Mark,

Name your list.

For instance if your list is named MyValidationList, then in the validation list textbox, enter

=MyValidationList

You list can be on ANY sheet.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[MarkBeck]

Try it and you will see that as long as the named range is on another sheet you are not prevented from entering other (invalid) date.

My question is how to stop Excel accepting Invalid data?

 

[SkipVought]

What are you doing?

I put my list on another sheet and I am prevented from entering ANY data other than data in my list!!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[bluedragon2]

Even though Excel help says to have a list on the same worksheet, try this:

in the data valadation formula box for list, put:

=<your named range>

and it will not matter what sheet it is on



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Bottom part of Excel help I see explains this:

If the list of valid entries is in another worksheet or workbook, define a name for the external data on the active worksheet. In the Source box, you can then refer to the name on the same worksheet. For example, if the list of valid entries is in cells A6:A12 on the first worksheet in a workbook named Budget.xls, you could define the name ValidData on the active worksheet to be =[Budget.xls]Sheet1!$A$6:$A$12 and then enter =ValidData in the Source box. For information about defining names, click



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Same as Skip - Same sheet or other sheet - Restricted to just the values in the relevant list.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[MarkBeck]

Thanks to both of you.

If you name A COMPLETE COLUMN (A:A) and use this as your validation you get the problem above. When you just name a smaller range (A1:A1000) you are both correct.

My sheets are quite large (I am an accountant) and my formulas usually take the form of &quot;A:A&quot; rather then &quot;A1:A1000&quot;. Therefore in order not to screw up with conditional formulas such as SUMIF, COUNTIF, i name complete columns as ranges.

Is there any way out?


Thanks


Mark

 

[SkipVought]

Mark,

Do you have data in row 65536 in you list? I DON'T THINK SO!

You can and SHOULD name a list DYNAMICALLY (ie, as you list grows, so does the corresponding RANGE associated with the name.

See faq68-1331 How can I rename a table as it changes size and us the OFFSET method to name your range.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[bluedragon2]

I think that what you have will work also, just uncheck the ignore blank when you create the data validation.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Ahhh - The light dawns. When you hit Data / Validation, uncheck the 'Ignore Blanks' option. This should fix that.

As an aside, if you start using array formulas at all (Very powerful things), then you cannot use Full Column references in array formulas (Just in case you come across it and wonder why it isn't working).

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[MarkBeck]

Make this Thanks to the THREE of you!

Hi Blue Dragon2

I have already spent hours upon hours on many occassions trying to make that particular piece of the help file actually work. When using the Worksheet part of the address in te name definition you are still allowed other entries, and when adding to the definition the document name as well you get an error message (MsgBox; The source currently evaluates to an error)


Mark

 

[MarkBeck]

Unticking &quot;Ignore Blanks&quot; does it! Thnks to both of you!

 

[bluedragon2]

Although it does work, I STRONGLY recommend that you follow Skip's advice and create dynamic ranges. As per Ken said, some things in Excel do not like them.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MarkBeck]

This was a first to me and i would most certainly start incorporating Dynamicly set tables in my sheets. I have a feeling that i will be using this a lot in various ways.

Thank you Skip and BlueDragon