Data Validation to exclude blank cells

[Simon]

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon

 

[Jacob Skaria]

--In Sheet2; in a unused Column Row1 (say cell I1) enter the below formula.
This will list all invoice numbers from Sheet1....without blanks

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=IF(COUNTA(Sheet1!$A$1:$A$1000)>=ROW(A1),
INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",
ROW(Sheet1!$A$1:$A$1000)),ROW(A1))),"")

--In Data>Validation>List>Source use the below formula

=OFFSET($I$1,,,SUMPRODUCT(--(I1:I1000<>"")))

 

[JLatham]

One solution is to take advantage of the second sheet's _Activate() event to
rebuild your data valdated cell's content. Take the code below and copy it
into your SHEET 2's code module, make any changes to the Const values needed
to tailor it to the two worksheets. Any time you select (activate) that
sheet, the list will be updated to match the entries on SHEET 1.

It's easy to put the code where it belongs: open the workbook, select your
SHEET 2 and right-click on it's name tab. Choose [View Code] from the list
and then simply copy the code below and paste it into the module presented
and make any edits required, then close the VB Editor. Select any other
sheet, then that sheet again and check out the contents of the data validated
cell.

Private Sub Worksheet_Activate()
'Change these constants to match your
'worksheets setup
'
'sheet with details
Const sourceSName = "Sheet1"
Const invoiceCol = "A"
Const invoice1stRow = 2
'the summary sheet
'cell to set up data
'validation into (on this sheet)
Const dvCellAddr = "A1"

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim dvCell As Range
Dim dvList As String

Set sourceSheet = Worksheets(sourceSName)
Set sourceRange = sourceSheet.Range(invoiceCol & _
invoice1stRow & ":" & _
sourceSheet.Range(invoiceCol & Rows.Count). _
End(xlUp).Address)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
dvList = dvList & anySourceEntry & ","
End If
Next
Set dvCell = ActiveSheet.Range(dvCellAddr)
With dvCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'housekeeping
Set dvCell = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub

 

[Simon]

Thanks to you both. Jacob, the Offset function didn't work for me, although
the array function did. The code sent in by JLatham worked splendidly.
Again, thanks both.

One solution is to take advantage of the second sheet's _Activate() event to
rebuild your data valdated cell's content. Take the code below and copy it
into your SHEET 2's code module, make any changes to the Const values needed
to tailor it to the two worksheets. Any time you select (activate) that
sheet, the list will be updated to match the entries on SHEET 1.

It's easy to put the code where it belongs: open the workbook, select your
SHEET 2 and right-click on it's name tab. Choose [View Code] from the list
and then simply copy the code below and paste it into the module presented
and make any edits required, then close the VB Editor. Select any other
sheet, then that sheet again and check out the contents of the data validated
cell.

Private Sub Worksheet_Activate()
'Change these constants to match your
'worksheets setup
'
'sheet with details
Const sourceSName = "Sheet1"
Const invoiceCol = "A"
Const invoice1stRow = 2
'the summary sheet
'cell to set up data
'validation into (on this sheet)
Const dvCellAddr = "A1"

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim dvCell As Range
Dim dvList As String

Set sourceSheet = Worksheets(sourceSName)
Set sourceRange = sourceSheet.Range(invoiceCol & _
invoice1stRow & ":" & _
sourceSheet.Range(invoiceCol & Rows.Count). _
End(xlUp).Address)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
dvList = dvList & anySourceEntry & ","
End If
Next
Set dvCell = ActiveSheet.Range(dvCellAddr)
With dvCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'housekeeping
Set dvCell = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon

 

[Jacob Skaria]

Thanks for the feedback Simon. The below function should return the number of
rows with data. Did you try the same formula directly in a cell? and if that
returns the number of rows with data; it should work fine in validation..

--
Jacob (MVP - Excel)

Thanks to you both. Jacob, the Offset function didn't work for me, although
the array function did. The code sent in by JLatham worked splendidly.
Again, thanks both.

One solution is to take advantage of the second sheet's _Activate() event to
rebuild your data valdated cell's content. Take the code below and copy it
into your SHEET 2's code module, make any changes to the Const values needed
to tailor it to the two worksheets. Any time you select (activate) that
sheet, the list will be updated to match the entries on SHEET 1.

It's easy to put the code where it belongs: open the workbook, select your
SHEET 2 and right-click on it's name tab. Choose [View Code] from the list
and then simply copy the code below and paste it into the module presented
and make any edits required, then close the VB Editor. Select any other
sheet, then that sheet again and check out the contents of the data validated
cell.

Private Sub Worksheet_Activate()
'Change these constants to match your
'worksheets setup
'
'sheet with details
Const sourceSName = "Sheet1"
Const invoiceCol = "A"
Const invoice1stRow = 2
'the summary sheet
'cell to set up data
'validation into (on this sheet)
Const dvCellAddr = "A1"

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim dvCell As Range
Dim dvList As String

Set sourceSheet = Worksheets(sourceSName)
Set sourceRange = sourceSheet.Range(invoiceCol & _
invoice1stRow & ":" & _
sourceSheet.Range(invoiceCol & Rows.Count). _
End(xlUp).Address)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
dvList = dvList & anySourceEntry & ","
End If
Next
Set dvCell = ActiveSheet.Range(dvCellAddr)
With dvCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'housekeeping
Set dvCell = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon

 

[Simon]

Hi Jacob, I tried various things to make it work, including entering the
formula directly into the cell, but got error messages. Could it be that the
Invoice number isn't necessarily a number (i.e. it is text)?

Thanks for the feedback Simon. The below function should return the number of
rows with data. Did you try the same formula directly in a cell? and if that
returns the number of rows with data; it should work fine in validation..

--
Jacob (MVP - Excel)

Thanks to you both. Jacob, the Offset function didn't work for me, although
the array function did. The code sent in by JLatham worked splendidly.
Again, thanks both.

One solution is to take advantage of the second sheet's _Activate() event to
rebuild your data valdated cell's content. Take the code below and copy it
into your SHEET 2's code module, make any changes to the Const values needed
to tailor it to the two worksheets. Any time you select (activate) that
sheet, the list will be updated to match the entries on SHEET 1.

It's easy to put the code where it belongs: open the workbook, select your
SHEET 2 and right-click on it's name tab. Choose [View Code] from the list
and then simply copy the code below and paste it into the module presented
and make any edits required, then close the VB Editor. Select any other
sheet, then that sheet again and check out the contents of the data validated
cell.

Private Sub Worksheet_Activate()
'Change these constants to match your
'worksheets setup
'
'sheet with details
Const sourceSName = "Sheet1"
Const invoiceCol = "A"
Const invoice1stRow = 2
'the summary sheet
'cell to set up data
'validation into (on this sheet)
Const dvCellAddr = "A1"

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim dvCell As Range
Dim dvList As String

Set sourceSheet = Worksheets(sourceSName)
Set sourceRange = sourceSheet.Range(invoiceCol & _
invoice1stRow & ":" & _
sourceSheet.Range(invoiceCol & Rows.Count). _
End(xlUp).Address)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
dvList = dvList & anySourceEntry & ","
End If
Next
Set dvCell = ActiveSheet.Range(dvCellAddr)
With dvCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'housekeeping
Set dvCell = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub


:

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon

 

[Jacob Skaria]

The below formula should return the number of rows with data in Col I..I hope
Col I is where you have applied the array formula from Row1....

=SUMPRODUCT(--(I1:I1000<>""))

The OFFSET formula starting from cell I1 should take a dynamic range for the
filled cells.


--
Jacob (MVP - Excel)

Hi Jacob, I tried various things to make it work, including entering the
formula directly into the cell, but got error messages. Could it be that the
Invoice number isn't necessarily a number (i.e. it is text)?

Thanks for the feedback Simon. The below function should return the number of
rows with data. Did you try the same formula directly in a cell? and if that
returns the number of rows with data; it should work fine in validation..

--
Jacob (MVP - Excel)

Thanks to you both. Jacob, the Offset function didn't work for me, although
the array function did. The code sent in by JLatham worked splendidly.
Again, thanks both.

:

One solution is to take advantage of the second sheet's _Activate() event to
rebuild your data valdated cell's content. Take the code below and copy it
into your SHEET 2's code module, make any changes to the Const values needed
to tailor it to the two worksheets. Any time you select (activate) that
sheet, the list will be updated to match the entries on SHEET 1.

It's easy to put the code where it belongs: open the workbook, select your
SHEET 2 and right-click on it's name tab. Choose [View Code] from the list
and then simply copy the code below and paste it into the module presented
and make any edits required, then close the VB Editor. Select any other
sheet, then that sheet again and check out the contents of the data validated
cell.

Private Sub Worksheet_Activate()
'Change these constants to match your
'worksheets setup
'
'sheet with details
Const sourceSName = "Sheet1"
Const invoiceCol = "A"
Const invoice1stRow = 2
'the summary sheet
'cell to set up data
'validation into (on this sheet)
Const dvCellAddr = "A1"

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim dvCell As Range
Dim dvList As String

Set sourceSheet = Worksheets(sourceSName)
Set sourceRange = sourceSheet.Range(invoiceCol & _
invoice1stRow & ":" & _
sourceSheet.Range(invoiceCol & Rows.Count). _
End(xlUp).Address)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
dvList = dvList & anySourceEntry & ","
End If
Next
Set dvCell = ActiveSheet.Range(dvCellAddr)
With dvCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'housekeeping
Set dvCell = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub


:

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon