Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- maryj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content of the cell. It selects the first blank in the dropdown list, because it is a match for the blank in the cell. To prevent this, you could fill the cell with the first item from the list, as a default value. Or, you can use a named dynamic range as the source. There are instructions he http://www.contextures.com/xlNames01.html Then, in the Data Validation Source box, type an equal sign and the name of the range, e.g.: =MyRange maryj wrote: I have created a data validation list that pulls values from cells E98:E112. E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- maryj "Debra Dalgleish" wrote: Your current data validation list contains blanks, and when the Data Validation dropdown is opened, it finds a match to the current content of the cell. It selects the first blank in the dropdown list, because it is a match for the blank in the cell. To prevent this, you could fill the cell with the first item from the list, as a default value. Or, you can use a named dynamic range as the source. There are instructions he http://www.contextures.com/xlNames01.html Then, in the Data Validation Source box, type an equal sign and the name of the range, e.g.: =MyRange maryj wrote: I have created a data validation list that pulls values from cells E98:E112. E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Instead of using COUNTA in the formula, you could use COUNTIF, e.g.:
=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"0") ,1) maryj wrote: Thanks Debra. I tried to create a dynamic range using the offset and the counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
I use this technique to limit the print area when I have formulas that evaluate
to "". Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) ) (Make that 2000 big enough to extend past the last possible row--but don't use the whole column.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5) That last 5 represents the last column to print (A:E). And change the worksheet (sheet1) if necessary (in all the places). === It works nicely when there are "" intermingled in the column. (Maybe not too nice a fit for you???) maryj wrote: Thanks Debra. I tried to create a dynamic range using the offset and the counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- maryj "Debra Dalgleish" wrote: Your current data validation list contains blanks, and when the Data Validation dropdown is opened, it finds a match to the current content of the cell. It selects the first blank in the dropdown list, because it is a match for the blank in the cell. To prevent this, you could fill the cell with the first item from the list, as a default value. Or, you can use a named dynamic range as the source. There are instructions he http://www.contextures.com/xlNames01.html Then, in the Data Validation Source box, type an equal sign and the name of the range, e.g.: =MyRange maryj wrote: I have created a data validation list that pulls values from cells E98:E112. E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Thanks Dave and Debra for your great suggestions. You guys are the best!!
What we ended up doing was rather than using the cells with the formulas linking to the other sheet, we created a dynamic range on sheet where the actual values were entered. This eliminated the issue of needing to use "". -- maryj "Debra Dalgleish" wrote: Instead of using COUNTA in the formula, you could use COUNTIF, e.g.: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"0") ,1) maryj wrote: Thanks Debra. I tried to create a dynamic range using the offset and the counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Dave, How would you do this with a fixed number of rows but dynamic COLUMNS?
"Dave Peterson" wrote: I use this technique to limit the print area when I have formulas that evaluate to "". Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) ) (Make that 2000 big enough to extend past the last possible row--but don't use the whole column.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5) That last 5 represents the last column to print (A:E). And change the worksheet (sheet1) if necessary (in all the places). === It works nicely when there are "" intermingled in the column. (Maybe not too nice a fit for you???) maryj wrote: Thanks Debra. I tried to create a dynamic range using the offset and the counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- maryj "Debra Dalgleish" wrote: Your current data validation list contains blanks, and when the Data Validation dropdown is opened, it finds a match to the current content of the cell. It selects the first blank in the dropdown list, because it is a match for the blank in the cell. To prevent this, you could fill the cell with the first item from the list, as a default value. Or, you can use a named dynamic range as the source. There are instructions he http://www.contextures.com/xlNames01.html Then, in the Data Validation Source box, type an equal sign and the name of the range, e.g.: =MyRange maryj wrote: I have created a data validation list that pulls values from cells E98:E112. E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
Go across and the use COLUMN instead of ROW
=LOOKUP(2,1/(Sheet1!$1:$1<""),COLUMN(Sheet1!$1:$1)) -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "GeorgeW" wrote in message ... Dave, How would you do this with a fixed number of rows but dynamic COLUMNS? "Dave Peterson" wrote: I use this technique to limit the print area when I have formulas that evaluate to "". Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) ) (Make that 2000 big enough to extend past the last possible row--but don't use the whole column.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5) That last 5 represents the last column to print (A:E). And change the worksheet (sheet1) if necessary (in all the places). === It works nicely when there are "" intermingled in the column. (Maybe not too nice a fit for you???) maryj wrote: Thanks Debra. I tried to create a dynamic range using the offset and the counta, except the counta doesn't work since Excel does not interpret the cells as being blank since they contain a formula. I'd love to hear any other suggestions you have. -- maryj "Debra Dalgleish" wrote: Your current data validation list contains blanks, and when the Data Validation dropdown is opened, it finds a match to the current content of the cell. It selects the first blank in the dropdown list, because it is a match for the blank in the cell. To prevent this, you could fill the cell with the first item from the list, as a default value. Or, you can use a named dynamic range as the source. There are instructions he http://www.contextures.com/xlNames01.html Then, in the Data Validation Source box, type an equal sign and the name of the range, e.g.: =MyRange maryj wrote: I have created a data validation list that pulls values from cells E98:E112. E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is copied down through E112. Only cells E98:E105 have values in them. When the dropdown for the data validation list is clicked, the scroll bar is at the very bottom of the list displaying empty values and I always need to scroll to the top of the list. Why is the top of the list not automatically displayed in the dropdown rather than the bottom of the list? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Data Validation | Excel Worksheet Functions | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |