Drop down list showing blanks

**blueice2627** · 08-12-2012, 03:46 PM

I am thinking what i am trying to do isn't doable, but i guess if anyone would know it would be someone on here...

I have a data sheet that inputs numbers daily with the date on the top of each column. The date is in a merged cell over 4 columns because i have 4 columns worth data.
i have used the data validation to create the drop down list over the row containing all of the dates, and for some reason, even though i have the "Skip Blanks" checked, it seems like it is giving me a blank spot for all the cells that the date is merged with...

Ex. cell B4:F4 is merged to include today's date. The drop down menu will show today's date for B4 and a blank spot for C4, D4, E4, and F4, before it goes onto the next date...

Let me know if what i want can happen (whether by VBA or not, i am not a VBA specialist but i can get around...)

Thanks in advance!

**jason.b75** · 08-12-2012, 05:03 PM

Can you attach a sample sheet replicating the problem?

Trying to replicate the problem, I don't get any blanks in the dropdown.

**FDibbins** · 08-12-2012, 05:14 PM

for a start off, its never a good idea to use merge and center on data, it can confise the formulas that reference them. rather use "format/center across selection". having said that, remove the merge/center, and then try your drop-down

**blueice2627** · 08-12-2012, 06:13 PM

I have attached the sample for you guys to look it.

**FDibbins** · 08-12-2012, 07:34 PM

this isnt exactly pretty, but it gives you what you want. i changed your DV range. go thru the file and let me know what you think?

**blueice2627** · 08-12-2012, 08:52 PM

that would actually be pretty good. I left my work already so i can't test it out right now, but what i would most likely do with it is have that code on the home sheet with the rest of my reference cells. when i enter in new data for the next day, my macro adds it to left by inserting new columns. Would i be able to use this and have new columns show up? I am guessing i would just make it the 365 days long and all of the lines that don't have a date will just show blank/error but those would be on the bottom of the list and as new days come in it just pushes everything down?

Or i might have to set up my Macro so that it re-enters the code every time i enter in a new day?

*not complaining, what you posted is very helpful, just looking at the different options i have and the shortest code always wins! :-)

**jason.b75** · 08-12-2012, 09:51 PM

With a dates listed in column A of a sheet called Dates, create a named range that refers to

=INDEX(Dates!A:A,MATCH(MIN(Data!$1:$1),Dates!A:A,0)):INDEX(Dates!A:A,MATCH(MAX(Data!$1:$1),Dates!A:A,0))

Then use that named range to populate your validation dropdown.

For future reference, "skip blanks" only ignores empty cells at the beginning or end of the range, not those between populated cells.