Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
--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 DataValidationListSource use the below formula =OFFSET($I$1,,,SUMPRODUCT(--(I1:I1000<""))) -- Jacob (MVP - Excel) "Simon" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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. "JLatham" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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) "Simon" wrote: 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. "JLatham" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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)? "Jacob Skaria" wrote: 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) "Simon" wrote: 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. "JLatham" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to exclude blank cells
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) "Simon" wrote: 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)? "Jacob Skaria" wrote: 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) "Simon" wrote: 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. "JLatham" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove blank cells from data validation drop down box | Excel Worksheet Functions | |||
exclude blank cells in a line graph | Charts and Charting in Excel | |||
Data Validation Blank Cells | Excel Worksheet Functions | |||
Data Validation on blank cells | Excel Worksheet Functions | |||
How do I exclude a value in data validation? | Excel Discussion (Misc queries) |