HELP!? Validation List with Variable Lengths & Invisible Empty Cells

  • Thread starter Thread starter RobPendulum
  • Start date Start date
R

RobPendulum

HELP 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
 
Use a dynamic formula, assume that the 40 rows are in Sheet2!A1:A40, do
insert>name>define
give it a name like MyList and in the refers to box put

=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A$1:$A$40),)

now in the validation use allow>list and use

=MyList

in the source box

That way you won't see empty rows at the end before it gets filled..

--

Regards,

Peo Sjoblom

RobPendulum said:
HELP 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


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



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