Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Remove blank spaces in data validation list+vba

Hi,

My situation is that, im using data validation list for showing list of values from oracle DB. I have put the LOV (taken from oracle DB) into column A of sheet 1. And i named that range (from A2:A1048576, A1 contain the column name) as range1. And i given that range name in the formula.(formula:="=range1") But the problem is, if columnA contain 25 values first and then if some values are deleted from the DB and contain 20 values. The data validation list shows the 20 items and blank space for the rest items in the end of the list. How can i discard this blank space

Anybody please help in solving this.

Thanks in advance


Post your answer or comment

comments powered by Disqus
Trying to create a drop down list using data validation, in the same sheet I have a list of data then blank cells (which contain data if a certain input is selected).

In the source for data validation I'm using =OFFSET($B$6,0,0,SUMPRODUCT($B:$B),1)

Problem is when I go to select the dropdown menu, it has the data I need but also a bunch of blank spaces which can be selected also.

So I'm trying to delete those blank spaces in the drop down menu.

Thanks in advance

Hi there,

I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A

This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!

What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.

How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?

I have tried adding "" and even a space to no avail.

Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)

--
Thanks & regards,
-pp-

How do I remove blank entries from the data validation list in C1?

The user will be inputting values only in cells A1-A4, A7-A10, & A13-A16, and the entries will be random. These cells will be empty initially.

The data validation list in C1 should show only the actual entries - as shown in the attached (Entry Right.jpg):
Entry Right.jpg

But all I can muster (by setting B1 thru B12 to the values in A1-A4, A7-A10, & A13-A16 respectively, naming the B1:B12 range, and referring to the named range as the data validation list source) is as shown in the attached (Entry Wrong.jpg), with all of the blanks:
Entry Wrong.jpg

Sorting, such as alphabetical sorting, is not required.

Thanks for any suggestions.

Hi there,

I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A

This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!

What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.

How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?

I have tried adding "" and even a space to no avail.

Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)

--
Thanks & regards,
-pp-

I'm trying to trim off blank values in a validation list.

On the setup page, there are 15 spaces for participant names (eg A1:A15). If only 4 participants are entered, I would like the data validation on the following page to only display those 4 names with no blanks.

I've been playing with arrays for the last 2 hrs with little luck. Any help would be appreciated!

Thanks

Joe

Hi,

I was wondering if there was some way to auto update or resync values in data validated lists which are dependant on information in other cells.

I attach my spreadsheet. My list in Cell F32 changes depending on what has been selected in cell F31. However, my problem is that, until you select cell F32, it still keeps the previous value on display, if you know what I mean. So! What I would like to happen, is when I select a different option in cell F31, then I would like cell F32 to update and show the first available option from the new list?

Range A1 : A10 (*every cell contains formula)

A1 | Mumbai
A2 | Chennai
A3 |
A4 | Delhi
A5 | Kolkata
A6 |
A7 | Bangalore
A8 |
A9 | Hyderabad
A10 | Kerala

data validation -----> Allow ------> List------> Source---->"A1:A10"

after applying the datavalidation ,I can see the drop down list with data as well as blank cells (i.e, A3, A6, A8)

how to remove that blank cells in drop down list

Reagrds
Arool

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?

Hey Everyone,

I was wondering if you would be able to help me be able to remove blanks from a data validation lst.

the list is one enough to be able to enter additional names based on how many names the user enters so the list length is alwas changing.

is their anyway to remove blancks from the list?

Cheers

My earlier post on this subject did not yield me an answer, so I am trying again. Here is the link to that post “Add Filter To Cascading Combo Boxes”. I hope it's not bad practice to start a new post instead of continue the old one. If it is, then I hope someone merges these threads instead of deleting this one.

This time instead of posting my real spreadsheet, I am posting a much simpler example, hoping for a better response.

Please download the attached example spreadsheet. On the workbook “Main” you will see cell “D2” is highlighted in yellow. That combo box (based on a data validation list) shows all the divisions that can be chosen. If you go to the workbook “CSI” you will see a sample of the data that I am working with. Each row of data that starts with a “00” is in Division 00, and each row that stars with “01” is in Division 01, etc.

Once a division is chosen in cell “D2”, then cell F2 looks up a named range. Then the green cells (D6 through D15) are limited to only those items that fall within the division that is chosen in cell “D2” by use of the data validation formula “=INDIRIECT($F$2)”

I am looking for a way to filter the results of these second combo boxes (data validation lists D6-D15) further. Instead of showing all the results of the named range which is shown in cell “F2” I would like it filtered. The filter would be based on whether or not the last column of data for any given row has a “1” in it. It doesn't have to be a "1", I'll accept any way that you can make this work. I am just using a "1" for this example. Whether or not a row contains a "1" in column "J" will change from time to time, and I am thinking that this will be an easy way to indicate, or flag, whether or not a row is "Preferred", but I'm open to other suggestions.

If the cell in that row, row “J” on worksheet “CSI”, contains a “1” then I would like it to be displayed in the data validation lists in cells D6 through D15. If the cell for that row of data is blank then I do not want it to show up in the data validation list combo box.

To complicate this further, I would like a way to easily switch this filter on and off. I imagine this would filter would be turned on or off based on what is selected in an option button group (like I’ve got highlighted in blue in cell “D4”, whose results are tied to cell F4), or through an additional combo box or data validation list.

I'm pretty stuck from moving forward on this project until I get over this hurdle, so I appreciate and help you can provide.

Thanks, Spence

Does anyone know how to make the drop down arrow that is generated when you make a list in data validation visible at all times? Right now, the drop down arrow is only visible when you select the particular cell to which the data validation has been applied.

This is frustrating me! I want to create a data validation list where some of the entries contain a comma. So I do like this:

which gives me what I want, namely entries containing commas:

I save the file and everything is fine... until I reopen it again. Then I see this:

and when I go back and check in the data validation dialog I see that Excel 2007 has converted my commas to semicolons:

What on earth is going on? I searched the forums to find an answer, but without any luck. I don't know if this is also happening with Excel 2003?

is it possible to directly fill the data validation list from a record set. (the data is coming from oracle database)

if possible please send me the code

Thanks in advance

In the spreadsheet I'm using a Data Validation list with dates from 01.10.2007 - 01.10.2008.

I wonder if it's possible to fix the Data Validation with today date, so the user don't have to scroll down to find it.

Hi - Can anyone identify & rectify the error
I gave this formula at Data->validation->List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data->validation->list range name "Biz")
c4 - is having a list (thro' data->validation->,list range name "Region")
D5- is having a list (thro' data->validation->list range = formula as above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

I have a cell that uses data validation (list) which points at a named range
that represents a range on a different worksheet.

I need to be able to reset the cell value to the first value on the named
range list. Here is my sample code, which isn't working:

S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1)

debug.print Sheet12.Range("E2").Validation.Formula1
results in the named range
=DatesForSelection14days

when I use the immediate window to set:
S = Sheet12.Range("E2").Validation.Formula1
then
debug.print Range(S)(1) I get
10/3/2008
which is correct.

But when the two lines of code run within the sub itself, on the second line
I get a run-time error 1004, method 'Range' of object '_worksheet' failed
huh?

Any advice greatly appreciated.
Thanks,
Keith

I have the checkbox for in cell dropdown checked (in data validation)
There are multiple sheets that have data validation lists in this workbook - none of them show the dropdown. If you try to input wrong info - you get the error.
I have another workbook that has data validation - working like a charm.

There has to be some setting for this workbook that I inadvertantly turned on or off.

Does this sound familiar to anybody?

ok, im going to apoligize in advance for the length of this post, but i rather give too much info than not enough.

i should start off by saying that i would consider myself rather advanced with formula editing, but very inexperienced with macros.

i have a worksheet that has a data validation list in it, and i want to write a macro that will select the next new value in that list. for example, if i have a list like this (and yes, it will be in alphabetical order):

apple
banana
banana
grape
orange
orange
orange
peach

and the current selection is banana, i want it to automatically find the next new value, so even if it has the first banana selected, i want it to move to grape.

the overall goal of this macro will be to:
select the next in the list,
the rest of the spreadsheet will automatically update based on the formulas,
then it will hide certain rows of the worksheet based on given values,
print the sheet,
unhide all rows,
select next value and repeat until it reaches the end of the list.

the print statement will look something like this:
Sheets("Statement").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
unhide will be:
    Cells.Select
    Selection.EntireRow.Hidden = False
so essentially, what i need help with is the selecting next in the list, then i will need to be able to hide a row based on whether or not the sum of columns e through i are greater than $10 in a given row, but im pretty sure i could figure out the hide statement.

thanks in advance for the help, and you guys have helped me more than you know just by searching through the forums.
-Ryan

Hi - Can anyone identify & rectify the error
I gave this formula at Data->validation->List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data->validation->list range name "Biz")
c4 - is having a list (thro' data->validation->,list range name "Region")
D5- is having a list (thro' data->validation->list range = formula as above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Would someone please help. Thanks.

I want the following 3 items in a data validation list. Note they have
commas in the items.

Apple, Kiwi, and Orange
Strawberry, Mango, and Banana
Pineapple, Banana, and Orange

I can't get data validation to display the comma in the item because it
takes the comma as a seperator for a new item. So typing the above show
the following list:

Apple
Kiwi
and Orange
Strawberry
Mango
and Banana
Pineapple
Banana
and Orange

I have to be able to type these items directly into the DV list, not
into cells where DV uses a cell reference.

Can this be done?

Thanks again.

Dear All,

I created a data validation list to select multiple items. These items get accumulated with a comma (like Item1, Item2, etc..) in the adjacent.

I have written a small VB code to apply autofilter on a data sheet based on these filter criteria. Below is the code.

 
If Target.Address = "$I$5" Then
    If Target.Value <> "All" Then
        Set r = sh.AutoFilter.Range
        r.AutoFilter Field:=10, Criteria1:=Me.Range("I5").Value
    End If
End If

If Target.Address = "$I$5" Then
    If Target.Value = "All" Then
        Set r = sh.AutoFilter.Range
        r.AutoFilter Field:=10
    End If
End If
This works fine it is a single selection. But does not work with multi-filtering with commas.

Please shed some light on how I can get around this. Bottomline, I want to autofilter items based on multiple options selected.

Thanks.

hello.. is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list?
thank you very much..

Hi and thanks alot in advance for your help

Is it possible to write a code that in data validation=> list, it would become more handy for user to find data more quickly in lengthy lists? For example if s/he writes the first letter of the name , the list goes to that letter (like the drop-down lists we see in internet). Can we set the focus of the list to a default data?

I want to refresh my existing values when i change the data validation list .
Is it possible??? For example if my list contained message1 and i changed it
to message2 , the changed should be reflected whereever i had selected
message1 from the list


No luck finding an answer? You could always try Google.