Creating a list box, removing blanks from source data

**erica686** · 12-03-2009, 12:09 AM

Hi,

I would like to create a list within cell A1, sheet 1, with the source being column B from sheet 2.

Column B is constantly being refreshed so the number of entries in column B changes. Therefore I would like the list to only show text in column B, and to remove any blank cells.

I would prefer not to use VBA.

Thanks.

**smalltime** · 12-03-2009, 01:23 AM

Do you want to use drop-down in Sheet1 A1?

1) Name the Range of Sheet 2 Column B [example: source]
2) Use data validation in SHeet1 A1 using =source

Hope it helps

**teylyn** · 12-03-2009, 01:28 AM

Or, create a dynamic range name that spans only the entries in column B. Assuming B1 is a header

Insert - Name - Define - MyRange - Refers to

=offset(B1,1,0,counta(B:B)-1,1)

Then use in column A

Data - Validation - List - Source

=MyRange

hth