I have Excel 2007. I have searched and found two threads that address this very problem:
1) Ignore Blanks & Duplicates In Dynamic Named Range
2) Stop Blanks In Drop Down List
Yet when I attempt to implement the solutions, I do not see the expected results.
I have a spreadsheet with a range of dates that changes every month. They are in cells L5:L35. In a 31-day month, all cells have data. But in a 30-, 29-, or 28-day month, those cells have empty cells at the bottom of the range.
I have it defined as a named range "Dates".
I do not want the blanks to show up in the dropdowns (which are in A5 - A50).
In "Formulas", "Name Manager", I have "Dates" defined as:
=OFFSET(Data!$L$5,1,0,MATCH("*",Data!$L$5:$L$100,1),1)
I also tried:
=OFFSET(Data!$L$5,1,0,COUNTA(Data!$L$5:$L$35)-1,1)
but each time, the blank still shows up in the dropdown.
FWIW, L5 has the date manually typed in on the first day of each month, then L6=L5+1, L7=L6+1, etc.
What am I missing here? Thanks.
Dynamic named range, ignoring blanks
-
-
-
Re: Dynamic named range, ignoring blanks
Please do not use [noparse]
[/noparse] tags for formulas. I have removed the tags this time.
From your description there is an error in the way you have implemented the dynamic ranges. They should be:
=OFFSET(Data!$L$5,0,0,MATCH("*",Data!$L$5:$L$35,1),1)
Or
=OFFSET(Data!$L$5,0,0,COUNTA(Data!$L$5:$L$35),1)
You had a 1 in the 2nd parameter, which is the RowOffset parameter. This means you were starting your range from L6 but counting the correct number so selecting upto L36 (a blank cell).
Just to double check:
When you say that in a 28, 29 or 30 day month they have empty cells at the bottom. Are they truely empty (ie a Blank Cell) or do they contain a formula returning ""?
What type of data do the cells contain when they have valid data? -
Re: Dynamic named range, ignoring blanks
Sorry about the code tags. Will remember in the future.
Regarding the empty cells at the bottom, this is how I'm testing:
1) I put "10/01/2010" in cell "L5" (and cells L6:L35 autofill with 10/02/2010 through 10/31/2010)
2) I open the dropdown in cell A5 and it shows all 31 days
3) I change the date in "L5" to 09/01/2010 (and cells L6:L35 autofill with 09/02/2010 through 09/30/2010)
4) I left click on cell L36 (that says "10/01/2010") and press "Delete" on the keyboard
5) I open the dropdown in cell A5 and it shows 30 days in September and a blank at the bottom of the list
Does that help? -
Re: Dynamic named range, ignoring blanks
You typed:
4) I left click on cell L36 (that says "10/01/2010") and press "Delete" on the keyboard
I assume that means L35, since that would be the 31st row and where 10/01/2010 would appear?
If that is the case then check the revised formulae for the ranges I posted above. You will need to use the COUNTA version as you have numeric values. I have just double checked that having replicated your description and it works. -
Re: Dynamic named range, ignoring blanks
Thank you. I got it to work.
I also had to put the formula in the "Data Validation" section, though (without the reference to the worksheet name). -
-
Re: Dynamic named range, ignoring blanks
Hello,
I am attempting to do something very simlar. However my blanks will appear randomly within the list. These blanks will contain a formulae returning text "" (Blank). I have attempted to use the COUNTIF function for my dynamic range but I can not get this to work.
Any help really really appreciated.
Kind Regards,
Thomas -
Re: Dynamic named range, ignoring blanks
Thomas,
While we welcome you to Ozgrid, our rules require that all questions are started in their own thread with an appropriate Search Friendly Key Word title. Please start your own thread and if necessary use a link to refer back to a previous thread if it contains information that helps explain your problem.
Thank you.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!