Named Range and Blanks 3

[Scott24x7]

All,
When I use Named Ranges in the Data Validation List Box type, if there are blanks in the Named Range of the data, they appear in the dropdown list box, even though I have "Ignore Blanks" checked on in the validation definition. Is there a way to stop blanks showing up when using a named range for the validation critieria?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[xlbo]

yup - don't have blanks in your named range

Ignore blanks refers to whether the validation allows blanks in the validated cell NOT the list of possible values

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

Thanks Geoff. Man, that's annoying.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[xlbo]

Blanks in lists is a very bad idea in any case - causes all kinds of issues as the data becomes unrelated. Why do you need to have blanks in there ?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

I don't want them in there... that's the problem. They are appearing when I use a named range for data selection from another sheet. I had wanted to select the whole range so that when new entries are added, my user doesn't have to "redefine" where the range covers.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[xlbo]

use dynamic named ranges then rather than static ones:

faq68-1331


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

Geoff,
That's cool. Thanks for the tip. But in this case, I'm not able to use a Macro. My client won't allow them, have to do everything in sheet, so they will just have to put up with blanks at the bottom of the range lists.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[xlbo]

Please read the FAQ again

Only the bottom section deals on applying named ranges via code

The 1st sections talk about doing it in a workbook - I use these ALL the time. They are incredibly useful

If you have a range of cells filled with names - lets say cells A1:A10. This range could grow or shrink dependant on changes.

In the named range definition box, rather than entering just a range, use the OFFSET formula to define a range dynamically

=OFFSET($A$1,,,COUNTA(A:A),1)

This will create range 1 column wide and the number of rows deep that there are entries in column A. Add an entry and the list grow. Remove one and it shrinks

The FAQ also uses INDIRECT to prevent any inserts / deletes corrupting the range reference by holding it as a string. This is optional but generally good practice

read the FAQ again proplerly

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[substitute]

Xlbo

That is just about the most amazing thing I've seen in Excel since someone showed me SUMIF (15 years ago). I never thought of putting a formula in a range name definition. And a great formula too. Using CountA to measure the extent of an area - didn't think of that either...

The world has just opened up for me, and I thought I was pretty good with Excel. Where I've begun using macros for certain tasks (to the distress of the security watchdogs) I might be able to use formulas again.

Thanks. If you drank in my local I'd buy you a beer.

Ian

 

[xlbo]

Ian - no probs - happy to share the knowledge !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

Geoff,
Interesting. I see the wisdome of this now. But, is there a way to limit the "bottom end" of the list? I have several "data ranges" that start in the same column (A), so that inserting a row doesn't interrupt other ranges. This could be really useful for me. For example, I have range A5 to A25 but only populate A5 to A13 right now. Then a A30 I have another range that goes from A30 to A120 with A30 to A97 populated. Will this method still work?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[xlbo]

shift your lists to be in seperate columns - that way there is no limit to how much they can expand or contract and the formula will work

If you have them on the same column then you need to specify start and end points for the COUNTA formula but other than restricting the number of potential entries, it should still work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I have a rule that I maintain:

One List or Table per sheet.

The reason that I do this, even if the list is only a few rows deep, is that I can more easily employ Excel's data access, data analysis and data reporting features.

It is ultimately, less cumbersome and more easily maintained.

REPORT sheets, however, can be as busy and "disjointed" as necessary, but the SOURCE DATA, be it a list or table, ought to be isolated and consistent (ALWAYS with ONE row of unique headings, ALWAYS starting in A1, ALWAYS having NO EMPTY ROWS OR COLUMNS) Makes life soooooooooo much easier!!!!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[xlbo]

hmmm - not quite so strict as that

I tend to employ a lookup sheet which can have multiple lists on it

Tables, I would agree - 1 to a sheet


however, as long as you are careful and know that there will not be more than x entries in a list, I guess there is no real harm in having multiple lists in 1 column - can;t really see why that would be necessary though so I would be tmepted to change it to have 1 list per colummn

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

Thanks guys. Always looking for better philosophy. While I've a lot of experience as a developer, I'm not very experieneced in Excel, so little tips like this are golden.
Cheers!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Weelllllll...

occasionally, I "cheat" and might put multiple lists on a sheet. But if I am using queries to load combos based on another combo selection, I'll definitely use separate sheets (tables), as a sheet acts as a table using MS Query.

BUT on the other hand, if the list itself, is a names range (not just the data in the list), the NAME can act as a table in MS Query

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[xlbo]

...only static named ranges though - dynamic named ranges aren;t recognised as valid tables for querying...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, I did not know that, since I rarely use named ranges in queries.

Good to know, though. Thanks!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}