Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Name Range
To All,
I have a named range that includes some blank cells that I am trying to eliminate from a drop down list on another worksheet. I have Googled the internet and searched through several Excel newsgroups and think the solution is the use of INDIRECT and Dynamic ranges, except using COUNTA stops at the first blank cell. Can someone point me in the right direction with either a Formula or VBA that will take a named range (I would like a Dynamic range - if possible), strip out the blank cells to use with a drop down data validation list. Thanks Harry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Name Range
I think you would need to build a contiguous list (no blank cells) in another
range to do this. As I recall, Named ranges with complex formulas don't work well in data validation. you can use a formula like =IF(COUNTA($F$1:$F$50)=ROW(),OFFSET($F$1,SMALL(IF ($F$1:$F$50<"",ROW($F$1:$F$50)),ROW())-1,0),"") to get a list with no blanks. This is an array formula and would be entered with Ctrl+Shift+enter rather than just enter. then drag fill down the column (entered in G1 and filled down in the example) Then your dynamic range would be Name: List1 Refersto: =Offset(Sheet1!$G$1,0,0,counta(Sheet1!$G$1:$G$50), 1) where G1 and down is where you formula is placed. -- Regards, Tom Ogilvy "Harry Stevens" wrote: To All, I have a named range that includes some blank cells that I am trying to eliminate from a drop down list on another worksheet. I have Googled the internet and searched through several Excel newsgroups and think the solution is the use of INDIRECT and Dynamic ranges, except using COUNTA stops at the first blank cell. Can someone point me in the right direction with either a Formula or VBA that will take a named range (I would like a Dynamic range - if possible), strip out the blank cells to use with a drop down data validation list. Thanks Harry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Name Range
Tom Ogilvy wrote:
I think you would need to build a contiguous list (no blank cells) in another range to do this. As I recall, Named ranges with complex formulas don't work well in data validation. you can use a formula like =IF(COUNTA($F$1:$F$50)=ROW(),OFFSET($F$1,SMALL(IF ($F$1:$F$50<"",ROW($F$1:$F$50)),ROW())-1,0),"") to get a list with no blanks. This is an array formula and would be entered with Ctrl+Shift+enter rather than just enter. then drag fill down the column (entered in G1 and filled down in the example) Then your dynamic range would be Name: List1 Refersto: =Offset(Sheet1!$G$1,0,0,counta(Sheet1!$G$1:$G$50), 1) where G1 and down is where you formula is placed. Tom, Thanks it works! Now I have to see where and how to use it. I was having problems with it until I reread your post and noticed the Ctrl+Shift=Enter that I missed before. This part I did not understand. Now that I have it working I am going to see if I can have the list change as I change budget years. Since each budget has different line items. I currently use =SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B11,INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&" '!D:D")) to get the money and expenses for that year..now if I can make the range of items change to reflect that budget year would be nice, but not necessary. Thanks Harry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation to range names for Chart Source Data | Charts and Charting in Excel | |||
Data Validation and Name Range | Excel Worksheet Functions | |||
Data Validation range | Excel Discussion (Misc queries) | |||
Range Name in Data Validation | Excel Programming |