Remove blank entries from a list box either using VBA or basic formula

[Celtic_Avenger]

Can anyone help with this?

Hi.

I've got a spead sheet that asks the user to enter details of worker
onto one sheet.

ie. Surname.............First Name............Age.........etc.etc.

This data is then used in a few pivot table reports.

One pivot table simply lists the workers full names for entry int
another sheer.

The cells asking for the name are have a validation setting to
listbox.

The only problem I have is that the list box range is predetermined
but the list may be from 1 names to 150 names.

When entering the name into the new sheet using the dropdown box, i
shows all blanks as well as the names entered.

I have tried to use the "ignore blanks" option in the validatio
settings, but this doesnt seem to work from pivot table results.

Is there another way to do this?



Celtic_Avenge

 

[Debra Dalgleish]

You could create a dynamic range definition instead, and it would expand
or contract as the pivot table changes. There are instructions here:

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