Excel-Data Validation 3

[HobbitK]

Hi everyone ...
This question comes from an answer to another post.
I was looking at the Data Validation feature.
With the options it lists, how would you allow Only Numbers?
I am just learning VBA, and advanced useage of Excel.
Thanks for any help
Michael

 

[DaleWatson123321]

Steps...

1) Under "Allow", choose "Custom".

2) Under "Formula", enter: =ISNUMBER(xxx) - where xxx refers to the current cell.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

Hi
There's possibly a couple of ways

1)From Allow choose list and have a list of the data you want to allow - unfortuantely this has to be on the same sheet so you can't have a list hidden away on another sheet (as far as I know xl2k) which would be handy

2) choose decimal and set the parameter you want. Fro any sensible number you might use greater than or equal to -999999999999999999999999

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Dale
that answers a point that i was going to add to my post regarding the fact that I've never looked at Custom so dunno what it does!!

So have a star on me just for the coincidence!!

By the way, am I right about validation lists having to be on the same sheet - nice feature if I am right, NOT!

anyway, you seem to have gone a bit quiet over the last month or two, or is it just me?

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - you CAN have a list on another sheet. All you have to do is give it a range name (see Dale, I'm fighting for your cause ;-) )
Then, in the list entry box enter
=rangename

TADAAAAAA !!!! Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[DaleWatson123321]

Loomah,

Thanks for the STAR !!!

As for your observation... "you seem to have gone a bit quiet over the last month or two" ...it's probably somewhat accurate. It's because I've undertaken a couple of "heavy-duty" tasks (through Tek-Tips) that have been keeping me more occupied than usual.

Geoff,

Thanks for "fighting the cause" of attempting to get ALL Excel users to recognize the VERY significant advantage of using range names.

I still "shudder" when I look back to a couple years ago when I worked with a couple of recent programming graduates - who were adamant that range names did NOT even exist in Excel, never mind appreciating their SIGNIFICANT importance - in all areas but ESPECIALLY when using VBA.

I certainly hope Microsoft and the teaching faculties have since learned about this HUGE omission from their Excel course, and are now including significant emphasis on the use of range names.

Regards, ...Dale

 

[Loomah]

xlbo
A star for you tooooooooo. Obviously I'll not be using the info as I still ain't working but I can't believe I didn't see the solution and I couldn't believe it didn't exist. This is most helpful!

Dale
Depends who does the training. I did a very good VBA course in 99 at a company called QA (the guy that trained me had just finished training microsoft support staff to support office 2k!!). I still use the course material for reference. They highlight the importance of Named ranges in the materials, so not everyone's been ignoring them. I certainly used named ranges more when I was doing that sort of thing for a living than I do now!

;-)

This is my cause - work smarter!!! thread68-463686 If a man says something and there are no women there to hear him, is he still wrong?

 

[DaleWatson123321]

Loomah,

Thanks for the feedback, and a STAR to you - and a "plug" for your link to the Keyboard Shortcuts site.

Regards, ...Dale

 

[HobbitK]

Thanks for all the quick answers
The more I browse this forum, the more I realize the importance of the Excel Range Names!
Next is to figure out Conditional Formatting.
Michael