Validation question

[Morrigan]

I have a table where column A looks like:

1. o.c. < 700 mm
2. <empty cell>
3. o.c. >= 700 mm
4. <empty cell>
5. o.c. < 700 mm with...
6. <empty cell>
7. o.c. >= 700 mm with...
8. <empty cell>

I want to create a validation (ie. a drop down list) that only includes cells that are filled with information (ie. do not want blank cells).


Thank you

[Morrigan]

Any good solution to this one?

[VBA Noob]

Sorry to be thick.

You want a pick list for an existing Table ??

VBA Noob

[Morrigan]

Users will have multiple similar drop-down lists, which are generated from multiple tables that are already created. Base on these different inputs, my spreadsheet will search for the proper values from these tables and perform different calculations.

Hope I am clear.


Thanks

[VBA Noob]

Hi,

Are you after something like this

http://www.contextures.com/xlDataVal02.html#Illegal

Basically you want the users selection to do a lookup and return a value from your table ??

VBA Noob

[Morrigan]

It's not exactly a simple LOOKUP(). It's more like LOOKUP() within LOOKUP() within LOOKUP(). I will show you one of the tables just for your information.

What I really want is to create a drop-down list where I can specific a range from my existing tables. Yet, I DO NOT want any blank cells (existing tables have blank cells within the range that I want) to be included. See attachment.

Thanks

[Carim]

Hi,

Quite difficult to decipher this *.gif ....

Do you mind uploading a zipped copy of your file ...?

HTH
Carim

[Morrigan]

See attachment

[Carim]

Hi,

Could attached file help ... blanks are moved at the bottom of data validation range ... ( deleted attachment which was not useful ...)

HTH
Carim

[Morrigan]

Hmm...where is the drop-down list?

[Carim]

Hi again,

Just want to warn you about the validation process itself ...
The input area where users will be selecting items from dropdown boxes cannot be the reference area where are stored the various choices ...

Ideally a separate sheet where you are storing valid choices can be kept away from users' eyes ...(in terms of modifications) and only appear in the dropdown boxes ...

HTH
Carim

[Morrigan]

I missed the point from your previous post, sorry. However, I do not want to create many more tables(or columns) for the validation purpose(ie. skipping blank cells).

Is there other approach that can save me some work?


Thanks

[Carim]

Hi,

Attached is your spreadsheet ...

HTH
Carim

[Morrigan]

Thanks, I guess you used named range. Yet, I don't understand what "=Validation!$A$1:$A$4" is doing.

Can you explain?

[Carim]

Hi,

As I told you your three validation lists are stored in a sheet called validation ...

HTH
Carim

[Morrigan]

Hmm...I only see one sheet (one tab) named "Input".

[Carim]

Yes ...
The second sheet is hidden ...
Alt F11
Click on second sheet
F4 to get properties
pick last property from list to make it visible again ...

http://www.j-walk.com/ss/excel/usertips/tip036.htm


HTH
Carim

[Morrigan]

I see thanks, but I am trying to avoid using these helper columns. As I add more tables, I will need more of these columns and expand some of them. As you see, this one table will need 3 of those columns. It gets messy pretty quick.

Anyway other work-around without having to use helper columns?


Thanks again

[Carim]

Impossible ... it is the basic principle of validation ...

On one hand, the valid choices ...
On the other hand, what the user inputs ...

HTH
Carim

[Morrigan]

Hmm...okay, I just didn't know you cannot skip blank cells in a validation function.

Thanks for all the help.

(I learnt how to turn off sheet in this exercise!)