Remove Blanks from Data Validation

Im having trouble removing blanks from a data validation list.

I have two worksheets, Materials and Prices.

On the materials page I have I have groups of items, which are all given defined names, theres around 10 groups of materials, all with about 10 or so items in each group of material.

On the Prices page, I have two combo boxes, the first selects the type of material using simple validation, the second displays the items within that type of material using the following formula;

=INDIRECT(B7)

Ive ensured ive ticked the box to ignore the blanks on both sets of data valdiation, but still displays the blanks.

When opening the second drop down box, because the defined name is in a range (i.e =Materials!$A$1036:$A$1065 ) it will also display the blanks.

Can anyone offer any advice on how to get around the blanks?

You mean you have blanks at the bottom of the list? If so, you will need to use a dynamic named range...

See the last section here: http://www.contextures.com/xlnames01.html

You can't remove those unless you create a separate list that removes the blanks and then point to that list for a named range.

How is it not working?

Is the list beginning at A1140?

Try then:

=OFFSET(Materials!$A$1140,0,0,MATCH("*",Materials!$A$1140:$A$1170,-1),1)

or

=OFFSET(Materials!$A$1140,0,0,COUNTA(Materials!$A$1140:$A$1170),1)

Hi, Take a look at attachment. 1st sheet. Eliminate blanks...

In the validation did you enter the source for the list as:

=List

where List is the name of the dynamic named range?

Highlight C7:C29 and go to Data > Validation
Use below stated formula

=OFFSET(INDIRECT(ADDRESS(ROW(INDIRECT(B7)),1,,,"Materials")),,,COUNTA(INDIRECT(B7)))

Would you like to mark your thread as solved?

Also have a look here:

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

Section called "Using Dynamic Lists"

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.