Validation List with Variable Lengths & Invisible Empty Cells
 

HELP!! Excel is driving me nuts. Is there any way to do the following:
I've got a drop-down Validation cell, which calls upon a List of
selectable resources (40 rows). I currently only have 20 resources in
the List, but I know that I'll be changing and adding resources (40
will be the max) .
The problem is that the drop-down list lists my 20 resources & the 20
empty cells. I've tried some crazy elaborate macros to have Excel
recognize only the listed resources and change the Validation
parameters... but with no luck.

I found the following tip/blurb here called: "Validation list without
empty cells using VBA in Microsoft Excel", but I'm not familiar with
VBA. Is this my solution? How do I implement this? I don't understand
what the author means by:
"Insert the following code in This Workbook module."

Any help is greatly appreciated!!

-Rob Taylor
CEo, Pendulum


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Validation List with Variable Lengths & Invisible Empty Cells
 

As answered in .excel.misc:

You can use a dynamic named range as the source for the data validation
list. There are instructions he

http://www.contextures.com/xlDataVal01.html

In step 2, follow the link to the instructions to create a dynamic range.

http://www.contextures.com/xlNames01.html#Dynamic


RobPendulum wrote:
HELP!! Excel is driving me nuts. Is there any way to do the following:
I've got a drop-down Validation cell, which calls upon a List of
selectable resources (40 rows). I currently only have 20 resources in
the List, but I know that I'll be changing and adding resources (40
will be the max) .
The problem is that the drop-down list lists my 20 resources & the 20
empty cells. I've tried some crazy elaborate macros to have Excel
recognize only the listed resources and change the Validation
parameters... but with no luck.

I found the following tip/blurb here called: "Validation list without
empty cells using VBA in Microsoft Excel", but I'm not familiar with
VBA. Is this my solution? How do I implement this? I don't understand
what the author means by:
"Insert the following code in This Workbook module."

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Validation List with Variable Lengths & Invisible Empty Cells
 

Now this is a cool idea, that's going to solve some problems I had recently.
Thanks for posting this,
Felix

"Debra Dalgleish" wrote in message
...
As answered in .excel.misc:

You can use a dynamic named range as the source for the data validation
list. There are instructions he

http://www.contextures.com/xlDataVal01.html

In step 2, follow the link to the instructions to create a dynamic range.

http://www.contextures.com/xlNames01.html#Dynamic


RobPendulum wrote:
HELP!! Excel is driving me nuts. Is there any way to do the following:
I've got a drop-down Validation cell, which calls upon a List of
selectable resources (40 rows). I currently only have 20 resources in
the List, but I know that I'll be changing and adding resources (40
will be the max) .
The problem is that the drop-down list lists my 20 resources & the 20
empty cells. I've tried some crazy elaborate macros to have Excel
recognize only the listed resources and change the Validation
parameters... but with no luck.

I found the following tip/blurb here called: "Validation list without
empty cells using VBA in Microsoft Excel", but I'm not familiar with
VBA. Is this my solution? How do I implement this? I don't understand
what the author means by:
"Insert the following code in This Workbook module."

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Validation List with Variable Lengths & Invisible Empty Cells
 

You're welcome. I'm glad it helped you.

Felix wrote:
Now this is a cool idea, that's going to solve some problems I had recently.
Thanks for posting this,
Felix

"Debra Dalgleish" wrote in message
...

As answered in .excel.misc:

You can use a dynamic named range as the source for the data validation
list. There are instructions he

http://www.contextures.com/xlDataVal01.html

In step 2, follow the link to the instructions to create a dynamic range.

http://www.contextures.com/xlNames01.html#Dynamic


RobPendulum wrote:

HELP!! Excel is driving me nuts. Is there any way to do the following:
I've got a drop-down Validation cell, which calls upon a List of
selectable resources (40 rows). I currently only have 20 resources in
the List, but I know that I'll be changing and adding resources (40
will be the max) .
The problem is that the drop-down list lists my 20 resources & the 20
empty cells. I've tried some crazy elaborate macros to have Excel
recognize only the listed resources and change the Validation
parameters... but with no luck.

I found the following tip/blurb here called: "Validation list without
empty cells using VBA in Microsoft Excel", but I'm not familiar with
VBA. Is this my solution? How do I implement this? I don't understand
what the author means by:
"Insert the following code in This Workbook module."

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html