Excel - Data Validation via hidden list? 2

[kwbMitel]

Excel - Data Validation via hidden list?

I know how to create a drop down list using data validation
I know how to hide rows/columns
I think I once figured out how to hide a sheet
I know how to reference another file altogether

Regardless, I've seen spreadsheets that didn't seem to use any of the hidden fields/sheet/files to perform data validation.

I assume the data is stored and referenced by a method unknown to me and difficult to find in the online help.

Can anyone point me in the right direction or tell me this is not possible so I can stop trying to find it?

**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

hi,

Data > Data tools > Data Validation--List... and enter a comma delimited list.

but what a horrible way to maintain a list. a real list on a sheet is much MUCH easier to maintain!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kwbMitel]

==> but what a horrible way to maintain a list. a real list on a sheet is much MUCH easier to maintain!!!

For my own use I agree 100%.

However, most of the time I'm looking for values that never change and the people that I rely on to populate the spreadsheet somehow keep finding the data list and accidentally deleting it.

Thanks Skip, I've already tried and it works as advertised.

**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

kwbMitel.[ ] I understand your conundrum.[ ] That horrible tradeoff between horrible programming practises and horrible users.

My general approach, which evolved gradually over many years and gnashed teeth, is to unlock all the cells I am allowing the user to enter data into, while leaving all other cells locked.[ ] I then "protect" the worksheet, but without a password.[ ] (Why no password?[ ] If a user really wants to screw up your spreadsheet there will be no stopping him:[ ] never stand between a lemming and a cliff.)

As a visual aid to the user I give these unlocked cells a very pale yellow background "fill".[ ] This colour is chosen so it can be seen on the screen but will not be visible when the worksheet is printed on a black&white printer.

 

[kwbMitel]

I stopped using protected cells the day a user simply copied everything into a new unprotected sheet because "they couldnt change the cells"



**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

@Deniall, way to go! I like ALL you tactics.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

kwbMitel,

Though I'm sure it wasn't funny when that occurred - copying to new workbook, it did give me a slight chuckle today. Thanks for the laugh

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57