I am confused :-?

geolefty

Board Regular
Joined
Mar 6, 2002
Messages
180
:confused:

I created a data validation range (list with a named range). That list is supposed to include an "in cell dropdown" but the dropdown does not show up.

The ultimate problem is that when a change is made in the named range (inside the range) that change is not reflected and no choice is available for that new entry since a dropdown is not there.

The range i creted the data validation for is about 14,000 rows by 1 column. I recreated the data validation on a smaller range and it still does not give me the in cell drop down.

Does this make sense? Any thoughts?

thanks in advance
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
geolefty said:
:confused:

I created a data validation range (list with a named range). That list is supposed to include an "in cell dropdown" but the dropdown does not show up.

The ultimate problem is that when a change is made in the named range (inside the range) that change is not reflected and no choice is available for that new entry since a dropdown is not there.

The range i creted the data validation for is about 14,000 rows by 1 column. I recreated the data validation on a smaller range and it still does not give me the in cell drop down.

Does this make sense? Any thoughts?

thanks in advance

Is the in cell drop down checked?
 
Upvote 0
YES

and to add to the strangeness, I copied out this problem column to another workbook and the drop down shows up perfectly fine.

I just do not get it.
 
Upvote 0
How about saving your file, exiting Excel, shutting down your computer, restarting it, and reopening the saved file in Excel -- let us see if that clears up the problem!
 
Upvote 0
I restarted and still no luck.

I am able to copy out the column or just a handful of cells into a blank file and the drop down is fine.

Although this gave me an idea. After a couple of attempts I was able to 'trick' excel.

The column in question was AD. I tried to copy all the sheets from the original book into a new one and then delete column AD from the new file and then copy the OLD column AD and insert copied cells. That did not work.

I then tried to copy a block of the data. NOPE. I then tried to extend the data validation to ALL of the cells (somehow it was in most but not all). STILL NO drop downs.

I then copied the columns in blocks, columns A-AC, then AD, then the rest of the columns. BINGO that worked (for now).

Something I forgot to mention was that the drop down DID appear early on in the history of this file.

I guess I found a solution (possibly temporary) but would love it if omeone could tell me why it happened in the first place.

thanks a bunch.
 
Upvote 0
i copied and pasted cells that were already validated but just not showing the drop down.

when I copied out either the column or a smaller range of cells the dropdown appeared. When I tried to copy the whole sheet - it did not work
 
Upvote 0
geolefty said:
i copied and pasted cells that were already validated but just not showing the drop down.

when I copied out either the column or a smaller range of cells the dropdown appeared. When I tried to copy the whole sheet - it did not work

By any chance was the column width or row height changed?

Ken
 
Upvote 0
still very confused

No change in row height or column width. In fact on the sheets it works the row height and column width can be varied at will.

So far I have figured out the following.

It works when I...
Copy entire column to another sheet in a new file.
Copy portions of range to another sheet in a new file.
Copy entire column to another sheet in the SAME file.
Copy portions of range to another sheet in the SAME file.


It DOES NOT work when I...
Copy entire column into another column on the same SHEET.
Copy portions of range to another part of the same SHEET.
Copy directly over exsiting column and range
Copy entire sheet to another workbook


thanks for the input still trying.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top