Drop-down menu not populating from Database sheet, please help

[ctroxtell]

Can anyone with better Excel experience tell me why when I enter data in a Database sheet and go to a linked sheet and click the drop-down list only a couple of fields are populated. The others are blank, I can sometimes get them to populate if I remove the data and enter it again other times it will do nothing. I'd be grateful for any information on what is going wrong or what I have done. I've uploaded the file here, as you can see I entered data in the "Database" sheet and when you go to "Glass" sheet the drop-down list only list 2 of the data entries. The data don't seem to like me... Thank you for anything you can tell me about this. When I convert the excel document to the newest format I get a "The name, TR2, either conflicts with a valid range reference or is invalid for Excel. The name has been replaced with _TR2. You may still need to manually update any references to this name used in VBA code or as text argument in functions."

[Danerida]

All your worksheets are protected.

[Danerida]

The formula you have in your actual list in AD4:AD15 of your database sheet (which is hidden), is not in every cell of that range i.e. there are some cells in that range which are blank. Copy the formula down and your data validation will work.

[ctroxtell]

I've uploaded an unprotected worksheet. It came from the website locked so you couldn't mess with it too much but I was able to unlock it. How do I view this AD4:AD15 list? Thank you again.

EDIT: Also do you know how to get it to stop complaining about this TR2 name error. Just fixed the Database sheet so you could edit the formatting in case it helps you look at it.

[newdoverman]

The workbook may be unprotected but there is still protected worksheets.

[ctroxtell]

I re-uploaded the unprotected sheet, I got the Database page worksheet unlocked. Is this the worksheet that needs to be unlocked? Thank you for all of your expertise. Everyone in my HVAC class will appreciate a working worksheet.

[Danerida]

I managed to crack your sheet protection anyway. AD4:AD15 is on your Database sheet (it's hidden between columns AC and AE). This is what your name is referring to for the data validation.
If you can't see the column names, go to the View Tab on the ribbon and check the Headings box.

As for the name TR2.... I'm not sure why it won't let you have it. You can have TR_2 as well as other variations.

Dan

[Pete_UK]

As for the name TR2.... I'm not sure why it won't let you have it. You can have TR_2 as well as other variations.

Dan

TR2 could be interpreted as a cell reference, so you can't have a named range in that format.

Hope this helps.

Pete

[ctroxtell]

Danerida, thank you so very much! I can only imagine the formula is getting deleted when editing the data. Is there a way to ensure those formulas don't get deleted? I would of thought that with these sheets being protected it wouldn't of allowed one to delete the formulas.

[Danerida]

My suggestion would be to have the lists on a separate sheet to anything that will be fiddled with by users. Dynamic lists would be even better, so if your data grows, they are automatically populated to match. Google Dynamic Named Ranges for more info.

Dan

PS: @Pete_UK good point. I didn't think of that.

[newdoverman]

" I would of thought that with these sheets being protected it wouldn't of allowed one to delete the formulas. "

It depends upon what was selected to be allowed when the worksheets were protected. Some things may be allowed after protection.

[ctroxtell]

Yeah, it's strange if I select the entire AD column and hit delete it won't allow it, but if I select the actual cell I can do whatever I want with it. I'm going to research the dynamic named ranges as this would be ideal so we are not having to constantly delete all of the data on the database to enter new data.